count and group by month - 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

General SQL Server Forums

New to SQL Server Programming

count and group by month

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-11-27 : 04:43:45

Hi i need a stored proc to do this

1. get a count of rows
2. group them into months

so i can display on my webpage

jan 48
feb 35
march 12

if the count is zero then i dont want to show anything at all

i have tried this

SELECT COUNT(SchProcessID) AS MonthCount, DATENAME(MONTH, SchSMS_Date) AS [MonthName]
FROM ScheduledMessageToSend
WHERE SchClubID = @ClubId
GROUP BY DATENAME(MONTH, SchSMS_Date)
ORDER BY SchSMS_Date

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-27 : 04:48:15

See which one you want

SELECT COUNT(SchProcessID) AS MonthCount, DATENAME(MONTH, SchSMS_Date) AS [MonthName]
FROM ScheduledMessageToSend
WHERE SchClubID = @ClubId
GROUP BY DATENAME(MONTH, SchSMS_Date)
HAVING COUNT(SchProcessID)>0
ORDER BY SchSMS_Date

SELECT COUNT(SchProcessID) AS MonthCount, DATEADD(MONTH,DATEDIFF(MONTH, 0,SchSMS_Date),0) AS [MonthName]
FROM ScheduledMessageToSend
WHERE SchClubID = @ClubId
GROUP BY DATEADD(MONTH,DATEDIFF(MONTH, 0,SchSMS_Date),0)
HAVING COUNT(SchProcessID)>0
ORDER BY SchSMS_Date

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2008-11-27 : 05:12:35

If the count is 0 then it won't appear so there's no need to worry
Your problem is trying to order by the date when it doesn't appear in the resultset.

SELECT COUNT(*) AS MonthCount, DATENAME(MONTH, SchSMS_Date) AS [MonthName]
FROM ScheduledMessageToSend
WHERE SchClubID = @ClubId
GROUP BY DATENAME(MONTH, SchSMS_Date)
ORDER BY DATENAME(MONTH, SchSMS_Date)

or
select MonthCount, left(yrmth,4) as yr, datename(mm,yrmth+'01') as MonthName
from
(
SELECT COUNT(*) AS MonthCount, convert(varchar(6),SchSMS_Date,112) as yrmth
FROM ScheduledMessageToSend
WHERE SchClubID = @ClubId
GROUP BY convert(varchar(6),SchSMS_Date,112)
) a
order by yrmth

Just take the yr out of the select if you only have one year.
or if it's across years
select MonthCount, datename(mm,'1900' + mth+'01') as MonthName
from
(
SELECT COUNT(*) AS MonthCount, right(convert(varchar(6),SchSMS_Date,112),2) as mth
FROM ScheduledMessageToSend
WHERE SchClubID = @ClubId
GROUP BY right(convert(varchar(6),SchSMS_Date,112),2)
) a
order by mth

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources