Finding the total number of items in a 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

Finding the total number of items in a month

Author

Topic

rc1138
Starting Member

35 Posts

Posted - 2010-05-06 : 23:21:49

Hi All,

Just having a really difficult time trying to count the number of activities per month for a whole year using an sql query

I'm trying to do just January and February for now to test it out w/o any luck

Here is my statement

SELECT (SELECT COUNT(MONTH(IncDate)) AS Expr1
FROM irIncident AS Jan
WHERE (IncDate = I.IncDate)
AND MONTH(1/1/1996) = 1) AS Jan,
(SELECT COUNT(MONTH(IncDate)) AS Expr1
FROM irIncident AS Feb
WHERE (IncDate = I.IncDate)
AND MONTH(1/1/1996) = 1) AS Feb) AS Feb
FROM irIncident AS I INNER JOIN
pdEmployee AS E ON I.EmpKey = E.EmpKey
WHERE (E.Department = '1000') AND (I.HlthCare = 'Y')

I want it to display with just the total in one row

January February
5 0

But it keeps displaying with multiple rows...

January February

2 0
2 0
3 0
3 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-06 : 23:33:14

you might want to include the Year else it will be counting all January for all the years.


SELECT	COUNT (CASE WHEN MONTH(IncDate) = 1 THEN 1 END) AS JAN,
	COUNT (CASE WHEN MONTH(IncDate) = 2 THEN 1 END) AS FEB
FROM 	irIncident AS I 
	INNER JOIN pdEmployee AS E ON I.EmpKey = E.EmpKey
WHERE 	E.Department 	= '1000'
AND 	I.HlthCare 	= 'Y'

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

rc1138
Starting Member

35 Posts

Posted - 2010-05-06 : 23:41:01

Awesome it works ! thanks so much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources