Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Teaser (Mode) 2

Status
Not open for further replies.
gmmastros

gmmastros

Programmer
Feb 15, 2005
14,901
US
Mean is the same as average. Median has been beaten to death. Let's write a query to calculate Mode.


Mode is essentially the value the occurs most often. For this teaser, let's write a query that calculates the Mode of a sample. For example, from the following set of values [1, 3, 6, 6, 6, 6, 7, 7, 12, 12, 17], 6 occurs most often, and is therefore the mode of this sample. If the mode is not unique, then we should return NULL. For example, in this set [1, 1, 2, 4, 4] 1 and 4 both occur most often, so the mode (returned from our query) should be NULL.

Code: 
Declare @Temp Table(Id Int, Value Int)

Insert Into @Temp Values(1,1) 
Insert Into @Temp Values(1,3)
Insert Into @Temp Values(1,6) 
Insert Into @Temp Values(1,6) 
Insert Into @Temp Values(1,6) 
Insert Into @Temp Values(1,6) 
Insert Into @Temp Values(1,7) 
Insert Into @Temp Values(1,7) 
Insert Into @Temp Values(1,12) 
Insert Into @Temp Values(1,12) 
Insert Into @Temp Values(1,17)

Insert Into @Temp Values(2,1) 
Insert Into @Temp Values(2,1) 
Insert Into @Temp Values(2,2) 
Insert Into @Temp Values(2,4) 
Insert Into @Temp Values(2,4)

The challenge is to write a query that calculates the Mode for each id. The output of this query should be...

[tt][blue]
Id Mode
----- ------
1 6
2 NULL
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Sort by date Sort by votes
select distinct x.id, y.value as mode
from @temp x
left join
(
select a.id, a.value, count(b.cv) as ccv
from (
select id, value, count(value) as cv
from @Temp
group by id, value
) a
inner join (
select id, value, count(value) as cv
from @Temp
group by id, value
) b
on a.id = b.id
and a.cv <= b.cv
group by a.id, a.value
having count(b.id) = 1
) y
on x.id = y.id

good one! :)

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Pff, in Excel this would be easier.

Christiaan Baes
Belgium

My Blog
"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett
 
Upvote 0 Downvote
>> Pff, in Excel this would be easier

Pffffffffftttt, Excel is too powerful. power point could handle this one [lol]


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]
 
Upvote 0 Downvote
Yeah, but Powerpoint is not a relational database.

Christiaan Baes
Belgium

My Blog
"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett
 
Upvote 0 Downvote
I just wonder why you think Excel is one? :)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Guaranteed to strike fear in the hearts of us mere mortals:

[blue]Programming Excel Services[/blue] by Alvin Bruney
Microsoft Press © 2007 (336 pages)
ISBN: 9780735624078
Outlining radically different ways of working that unleash what was formerly impossible, this guide will help you learn how to use Excel Services to add spreadsheets and workbooks to your enterprise dashboards and portals.
Click to expand...

Plus, now they've added cube functions!

EEK!


< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]
 
Upvote 0 Downvote
I don't see where Relational Database is mentioned. Could you build a DataBase with Excel? Could you add referential integrity? Could you have triggers?

I don't deniy the Excel is good and powerful application, but RDBMS? I doubt about it.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
---> XNF

Talk about mere mortals not being ready for something!

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Star to Alex for writing a query that generates the correct results. I think you can do better (from a performance perspective).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
This is what I came up with.

[spoiler highlight see code]
Select Id, Case When Count(*) > 1 Then NULL Else Max(Value) End As Mode
From (
Select A.Id, A.Value
From (Select Id, Value, Count(*) As CountOfValue
From @Temp
Group By Id, Value
) as A
Inner Join (
Select Id, Max(CountOfValue) As MaxCountOfValue
From (
Select Id, Value, Count(*) As CountOfValue
From @Temp
Group By Id, Value
) As A
Group By Id
) As B
On A.Id = B.Id
And A.CountOfValue = B.MaxCountOfValue
) As A
Group By A.Id

[/spoiler]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I am sure someone can do better, but:

select y.id, case when count(*) > 1 then null else max(y.value) end
from
(
select id, value from(
select ID, value, count(value) as cv
from @temp
group by ID, value
) n
where n.cv = (select max(cv) from (select ID, value, count(value) as cv
from @temp
group by ID, value) y
where ID = n.id)

) y
group by y.id

I'm surprised by how few takers on this one...

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
It's Friday, Alex. I'm busy fine-tuning my fantasy football lineup.
 
Upvote 0 Downvote
Like it'll help you ;-)

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Here is mine:
select ID, case C when 1 then V else null end MODE from
(select ID, max(V) V, count(*) C from
(select ID, VALUE V, count(*) C from @TEMP T
group by ID, VALUE
having count(*)=(select max(C) from (select count(*) C from @TEMP where ID=T.ID group by ID, VALUE) T2)
) T
group by ID
) T
 
Upvote 0 Downvote
And a shorter one:
select ID, case count(*) when 1 then max(V) else null end MODE from
(select ID, VALUE V from @TEMP T
group by ID, VALUE
having count(*)=(select max(C) from (select count(*) C from @TEMP where ID=T.ID group by ID, VALUE) T)
) T
group by ID
 
Upvote 0 Downvote
Nice Otto!

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
339
pjw001
pjw001
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
364
Volvere
V
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
234
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
T-SQL Dialog Box
Replies
8
Views
208
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
257
SaltyTheFrog
SaltyTheFrog

Part and Inventory Search

Sponsor

Back
Top