Mode of columns grouped by seperate column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Mode of columns grouped by seperate column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-17 : 08:42:39

William writes "Maybe I'm overlooking something, and this is really brain-dead simple. It should be.
How can I, in a single SQL query (well, more if we must, I'm sure I could write it as a stored procedure if I had to, but a friend of mine and I... ah, enough backstory), get the most common element in each of several several columns that are grouped by a different column?
Example:


"Name", "Type", "A", "B", "C",  "D"
Jackson,  1,     1,   2,   3,   4
Houston,  1,     1,   2,   3,   4
New York, 1,     2,   3,   4,   1
Oklahoma, 2,     1,   2,   3,   4
Kansas,   2,     2,   1,   3,   2
Oregon,   2,     2,   2,   3,   4
France,   3,     1,   3,   3,   4
Italy,    3,     3,   1,   2,   4

etc, etc.
I would like, one type per row, the most common element in each column A, B, C, and D for each type 1, 2, 3, without, of course, knowing what types I have to begin with. Also, because of a poor design decision made in the depths of time, the type can be null.
(I came up with a union that would get it if I knew the types ahead of time. But when I tried to make that into a subquery, the cross joins I was using didn't seem to pass the right data back.)
SQL Server 2000, Windows 2000 SP 4."

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-20 : 12:18:43

How do you want to deal with ties, with no clear "common element?"

Examine mode A for type 3 in your example, do you want both values returned or neither?

And since NULLs are present, should these be ignored or included in the mode evaluation? Can NULL be considered a possible mode value?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-20 : 16:33:53

Here is one ugly way:


----------------------------------------------------
--DDL, DML
set nocount on
declare @tb table
	(Name varchar(15)
	,Type int
	,a int
	,b int
	,c int
	,d int)
insert @tb
select 'Jackson',  1,     1,   2,   3,   4 union all
select 'Houston',  1,     1,   2,   3,   4 union all 
select 'New York', 1,     2,   3,   4,   1 union all
select 'Oklahoma', 2,     1,   2,   3,   4 union all
select 'Kansas',   2,     2,   1,   3,   2 union all
select 'Oregon',   2,     2,   2,   3,   4 union all
select 'France',   3,     1,   3,   3,   4 union all
select 'Italy',    3,     3,   1,   2,   4
----------------------------------------------------


select	type
	,max(elem) mostCommonElement 
	--min,max doesn't matter because of top 1 in subquery
from	(
	select type, a elem from @tb union all
	select type, b from @tb union all
	select type, c from @tb union all
	select type, d from @tb
	) a
where	elem in 
	(select top 1 elem
	from	(
		select type, a elem from @tb union all
		select type, b from @tb union all
		select type, c from @tb union all
		select type, d from @tb
		) b
	where	type = a.type
	group by elem
	order by count(*) desc

	--ties will return max(element)
	, elem desc
	) 
group by type

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources