[Resolved] Want to count rows by month

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

General SQL Server Forums

New to SQL Server Programming

[Resolved] Want to count rows by month

Author

Topic

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2014-10-20 : 00:56:50

Hello

I am trying to get the number of rows for each month.

table name:sitez
ID Name crDate access
===========================
1 Bob .. 2014-01-11 .. 1
2 Jerry .. 2014-01-22 .. 2
3 Jim .. 2014-05-06 .. 1
4 Jason .. 2014-12-11 .. 1
5 Jen .. 2014-11-21 .. 3

I am using the results to make a bar graph so I am querying the database for a given year and expecting 12 results back (running SQL 2012).

Select count(*) as ttl FROM sitez WHERE year(crdate) = 2014 and access = 1 group by all month(crdate)

This should return:
1
0
0
0
1
0
0
0
0
0
0
1

However when testing the script I was only getting back:
1
1
1
which didn't help in knowing which months were 0

By changing the GROUP BY to GROUP BY ALL, it now puts in the zeroes. However I'm still having issues with incorrect results.

When I query the database, the results for December 2014 shows '13' when I execute:

Select count(*) as ttl FROM sitez WHERE year(crdate) = 2014 and access =3 group by all month(crdate)

There are ZERO rows made with a year of 2014 and an access of 3.I verified this by going a straightforward select * from sitez where crdate = 2014 and access = 3

Could anyone advise why I'm seeing ghost results?

All I need is 12 results, ordered by crdate month.
Thank you

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-10-20 : 01:50:04

[CODE]
;with cteMonths as
( SELECT 1 mnth
union all
SELECT mnth+1 FROM cteMonths where mnth <12)
Select SUM(CASE WHEN mnth = month(crdate) then 1 else 0 end) as ttl
FROM cteMonths
LEFT JOIN sitez ON 1=1
WHERE year(crdate) = 2014 and access = 1
group by mnth
[/CODE]

--
Chandu

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2014-10-20 : 02:35:30

I'm not sure what half of that does, but it worked 100% :)

thank you

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-10-20 : 03:10:45

Explanation for the above solution:

;with cteMonths as
( SELECT 1 mnth
union all
SELECT mnth+1 FROM cteMonths where mnth <12)

This part will generate 12 months number starts from 1 to 12...

FROM cteMonths
LEFT JOIN sitez ON 1=1

This results always 12 records ( from cteMonths CTE )

SUM(CASE WHEN mnth = month(crdate) then 1 else 0 end)
This will count the number of records in each month of CreateDate.....

Group BY mnth groups the records..
--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources