multiple join/use on/of same table

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)

multiple join/use on/of same table

Author

Topic

hephie
Starting Member

15 Posts

Posted - 2008-12-07 : 16:35:18

I have a table named test. It exists out of 2 columns, an ID (primary key) and a date time field.

Using one single sql statement I would like to retrieve the amount (count) of records per month. Each month needs to represent a column (as result out of the select statement).

Output example:

I think the sql statement should look something like this. The one i'm below isn't working of course, that's why I need help. I can't figure out the right way to do it. The results are never correct.
Hopefully someone can help me! Many thanks in advance!

The (wrong) query i'm using for the moment

[CODE]
SELECT
COUNT(T1.DatePlayed),
COUNT(T2.DatePlayed)
FROM
TEST T1,
TEST T2
WHERE
T1.DatePlayed BETWEEN '2008-10-01' AND '2008-11-01' AND
T2.DatePlayed BETWEEN '2008-11-01' AND '2008-12-01'[/CODE]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-07 : 16:40:54

[code]SELECT SUM(CASE WHEN DATEPART(MONTH, DatePlayed) = 1 THEN 1 ELSE 0 END) AS Januari,
SUM(CASE WHEN DATEPART(MONTH, DatePlayed) = 2 THEN 1 ELSE 0 END) AS Fabruari,
SUM(CASE WHEN DATEPART(MONTH, DatePlayed) = 3 THEN 1 ELSE 0 END) AS Maart,
SUM(CASE WHEN DATEPART(MONTH, DatePlayed) = 4 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN DATEPART(MONTH, DatePlayed) = 5 THEN 1 ELSE 0 END) AS Mei,
SUM(CASE WHEN DATEPART(MONTH, DatePlayed) = 6 THEN 1 ELSE 0 END) AS Juni,
SUM(CASE WHEN DATEPART(MONTH, DatePlayed) = 7 THEN 1 ELSE 0 END) AS Juli,
SUM(CASE WHEN DATEPART(MONTH, DatePlayed) = 8 THEN 1 ELSE 0 END) AS Augustus,
SUM(CASE WHEN DATEPART(MONTH, DatePlayed) = 9 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN DATEPART(MONTH, DatePlayed) = 10 THEN 1 ELSE 0 END) AS Oktober,
SUM(CASE WHEN DATEPART(MONTH, DatePlayed) = 11 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN DATEPART(MONTH, DatePlayed) = 12 THEN 1 ELSE 0 END) AS December
FROM Test
WHERE DatePlayed >= '20081001'
AND DatePlayed < '20081201'[/code]

E 12°55'05.63"
N 56°04'39.26"

hephie
Starting Member

15 Posts

Posted - 2008-12-07 : 16:46:29

NICE!!! Very fast reply! many thanks!
I just had to adjust it for mysql.

for mysql it's:

SELECT SUM(CASE WHEN MONTH(DatePlayed) =  1 THEN 1 ELSE 0 END) AS Januari,
SUM(CASE WHEN MONTH(DatePlayed) =  2 THEN 1 ELSE 0 END) AS Fabruari,
SUM(CASE WHEN MONTH(DatePlayed) =  3 THEN 1 ELSE 0 END) AS Maart,
SUM(CASE WHEN MONTH(DatePlayed) =  4 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN MONTH(DatePlayed) =  5 THEN 1 ELSE 0 END) AS Mei,
SUM(CASE WHEN MONTH(DatePlayed) =  6 THEN 1 ELSE 0 END) AS Juni,
SUM(CASE WHEN MONTH(DatePlayed) =  7 THEN 1 ELSE 0 END) AS Juli,
SUM(CASE WHEN MONTH(DatePlayed) =  8 THEN 1 ELSE 0 END) AS Augustus,
SUM(CASE WHEN MONTH(DatePlayed) =  9 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN MONTH(DatePlayed) = 10 THEN 1 ELSE 0 END) AS Oktober,
SUM(CASE WHEN MONTH(DatePlayed) = 11 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN MONTH(DatePlayed) = 12 THEN 1 ELSE 0 END) AS December
FROM TEST
WHERE DatePlayed >= '20081001'
AND DatePlayed < '20081201'

regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-07 : 16:48:11

Why are you posting Sun MySQL question in a Microsoft SQL Server forum?

E 12°55'05.63"
N 56°04'39.26"

hephie
Starting Member

15 Posts

Posted - 2008-12-07 : 16:49:32

owww
woops
didn't see

searchred google for sql forum, quickly registered, quickly posted my question.
:)

sorry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources