selecting totals - SQL Server Forums

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)

selecting totals

Author

Topic

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-05-20 : 09:42:50

Okay, so i have a table like below

tblScores
email, distance, playDate

with example data as below

test@test.com , 25, 12-01-2009
test@test.com , 23, 13-01-2009
test@test.com , 45, 12-03-2009
test@test.com , 51, 11-01-2009
test2@test.com , 65, 12-01-2009
test2@test.com , 24, 13-03-2009
test2@test.com , 45, 12-03-2009
test2@test.com , 52, 11-01-2009

what i want to do is select all the highest distances for each email address within a date range and also add a total row count. so the above table would come out as follows if i searched for january

test@test.com , 51, 11-01-2009, 3
test2@test.com , 65, 12-01-2009, 2

I can do the date range bit, what I'm having trouble with is creating the last column (the total number of occurrences of an email address during a date range) and also selecting the highest distance

any help is appreciated

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-20 : 09:53:06

[code]
DECLARE @tblScores TABLE
(
email varchar(20),
distance int,
playDate datetime
)

INSERT INTO @tblScores
SELECT 'test@test.com' , 25, '12-01-2009' UNION ALL
SELECT 'test@test.com' , 23, '13-01-2009' UNION ALL
SELECT 'test@test.com' , 45, '12-03-2009' UNION ALL
SELECT 'test@test.com' , 51, '11-01-2009' UNION ALL
SELECT 'test2@test.com' , 65, '12-01-2009' UNION ALL
SELECT 'test2@test.com' , 24, '13-03-2009' UNION ALL
SELECT 'test2@test.com' , 45, '12-03-2009' UNION ALL
SELECT 'test2@test.com' , 52, '11-01-2009'

SELECT s.*, m.cnt
FROM @tblScores s
INNER JOIN
(
SELECT email, distance = MAX(distance), cnt = COUNT(*)
FROM @tblScores
WHERE playDate >= '2009-01-01'
AND playDate <= '2009-01-31'
GROUP BY email
) m ON s.email = m.email
AND s.distance = m.distance
WHERE playDate >= '2009-01-01'
AND playDate <= '2009-01-31'
ORDER BY email DESC

/*
email distance playDate cnt
-------------------- ----------- ------------------------------------------------------ -----------
test@test.com 51 2009-01-11 00:00:00.000 3
test2@test.com 65 2009-01-12 00:00:00.000 2

(2 row(s) affected)
*/
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources