How to get SQL to return Excel mode() function

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 2005 Forums

Transact-SQL (2005)

How to get SQL to return Excel mode() function

Author

Topic

daman
Yak Posting Veteran

72 Posts

Posted - 2007-10-26 : 20:16:01

in Excel, using Mode(number1,number2,...) I can get the most represented number in a range.

If I have a group of tables which have (value,date), I would like to go to Day 1 and get the Mode(value1,value2,...,value5) and then do the samething for Day 2.

Table 1	Value	Date
	14	3/21/2007
	16	3/22/2007
	15	3/23/2007
		
Table 2	Value	Date
	14	3/21/2007
	15	3/22/2007
	15	3/23/2007
		
Table 3	Value	Date
	15	3/21/2007
	16	3/22/2007
		
Result 	Value	Date
	14	3/21/2007
	16	3/22/2007
	15	3/23/2007

Since table 3 is missing 3/23/2007, I would insert the mode() of that date into the table. After this, replace each tables with Result table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-27 : 08:28:08

[code]DECLARE @Table1 TABLE (Value int, Date datetime)
DECLARE @Table2 TABLE (Value int, Date datetime)
DECLARE @Table3 TABLE (Value int, Date datetime)

INSERT INTO @Table1
SELECT 14, '3/21/2007' UNION ALL
SELECT 16, '3/22/2007' UNION ALL
SELECT 15, '3/23/2007'

INSERT INTO @Table2
SELECT 14, '3/21/2007' UNION ALL
SELECT 15, '3/22/2007' UNION ALL
SELECT 15, '3/23/2007'

INSERT INTO @Table3
SELECT 15, '3/21/2007' UNION ALL
SELECT 16, '3/22/2007'

;WITH Tbl(Value, Date)
AS
(
SELECT Value, Date
FROM @Table1

UNION ALL

SELECT Value, Date
FROM @Table2

UNION ALL

SELECT Value, Date
FROM @Table3
)
SELECT [Date], Value, cnt, row
FROM
(
SELECT Value, [Date], cnt = COUNT(*), row = row_number() OVER (PARTITION BY Date ORDER BY COUNT(*) DESC)
FROM Tbl t
GROUP BY [Date], Value
) a
WHERE row = 1
/*
Date Value cnt row
----------- ----------- ----------- ---------
2007-03-21 14 2 1
2007-03-22 16 2 1
2007-03-23 15 2 1
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

daman
Yak Posting Veteran

72 Posts

Posted - 2007-10-27 : 15:54:53

khtan,
it's amazing. Thanks very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources