SQL MULTIPLE COUNTS, GROUP BY MONTHS

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

SQL MULTIPLE COUNTS, GROUP BY MONTHS

Author

Topic

dejan88
Starting Member

7 Posts

Posted - 2012-04-17 : 08:00:50

I have table:

STATUS DEFECT DATE

CLOSED IP 01.01.2012
CLOSED TV 03.03.2012
CLOSED ADSL 05.05.2012
CLOSED ADSL 11.01.2012
CLOSED TV 15.01.2012
NEW TV
NEW TV

I want to group this by months with count for each specific DEFECT. Status which is considered is CLOSED

Resulting table I would like to be is:

MONTH TV ADSL IP
January 1 1 1
March 1 0 0
May 0 1 0

I am using db2 database so the part for displaying months which works is:

select case month(timestamp_iso(DATE))
when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December'
end as Month

from TABLE
where STATUS='CLOSED'
group by month(timestamp_iso(DATE))
order by month(timestamp_iso(DATE))

This displays only months in one column.

So I just need to add this part for counting (columns TV,ADSL,IP).Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-17 : 08:21:23

Something like this?

;WITH cteSource(theMonthName, theMonthNumber, Defect)
AS (
	SELECT	DATENAME(MONTH, [Date]) AS theMonthName,
		DATEPART(MONTH, [Date]) AS theMonthNumber,
		Defect
	FROM	dbo.Table1
	WHERE	[Status] = 'Closed'
)
SELECT		theMonthName,
		SUM(CASE WHEN Defect = 'TV' THEN 1 ELSE 0 END) AS TV,
		SUM(CASE WHEN Defect = 'ADSL' THEN 1 ELSE 0 END) AS ADSL,
		SUM(CASE WHEN Defect = 'IP' THEN 1 ELSE 0 END) AS IP
FROM		cteSource
GROUP BY	theMonthName,
		theMonthNumber
ORDER BY	theMonthNumber

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-17 : 13:15:04

I am using db2 database
this is ms sql server forum so you may be better off posting it in some db2 forums like www.dbforums.com if given suggestion doesnt work. People here are generally specialised in sql server and solutions given are guaranteed to work in SQL Server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources