Author |
Topic |
andypgill
Starting Member
34 Posts |
Posted - 2013-02-01 : 10:48:53
|
Hi AllCan anyone help me with my code.I'm trying to create a pivot.What I want it to do is display the filed [POD Codes ABC] and place the count of them in the relevant period column.This is my code.SELECT [POD Codes ABC], (case [MONTH] when '2012-04' then COUNT ([POD Codes ABC]) else 0 end ) M4,(case [MONTH] when '2012-05' then COUNT ([POD Codes ABC]) else 0 end ) M5,(case [MONTH] when '2012-06' then COUNT ([POD Codes ABC]) else 0 end ) M6,(case [MONTH] when '2012-07' then COUNT ([POD Codes ABC]) else 0 end ) M7FROM dbo.SLAMwhere [pod codes abc] = 'HAA3040D'group by [POD Codes ABC],[month]This gives mePOD Codes ABC M4 M5 M6 M7HAA3040D 343 0 0 0HAA3040D 0 353 0 0HAA3040D 0 0 86 0HAA3040D 0 0 0 1How do I write the code so it gives me one line per [POD Codes ABC]egPOD Codes ABC M4 M5 M6 M7HAA3040D 343 353 86 1Thanks |
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2013-02-01 : 11:57:11
|
HiSorry have to rush off but here is a few links to questions I had on here for pivots, might/might not be relevant but hope it helps:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164981&SearchTerms=pivothttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165475&SearchTerms=pivothttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=175001&SearchTerms=pivot |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-01 : 17:31:33
|
Move the COUNT function to outside the CASE expression like this:SELECT [POD Codes ABC], COUNT (case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4,.... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-02 : 03:14:30
|
should beSELECT [POD Codes ABC], SUM(case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4,SUM(case [MONTH] when '2012-05' then [POD Codes ABC] else 0 end ) M5,SUM(case [MONTH] when '2012-06' then [POD Codes ABC] else 0 end ) M6,SUM(case [MONTH] when '2012-07' then [POD Codes ABC] else 0 end ) M7FROM dbo.SLAMwhere [pod codes abc] = 'HAA3040D'group by [POD Codes ABC] if you're looking only for a particular code like HAA3040D then no need of GROUP BYjust do SELECT SUM(case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4,SUM(case [MONTH] when '2012-05' then [POD Codes ABC] else 0 end ) M5,SUM(case [MONTH] when '2012-06' then [POD Codes ABC] else 0 end ) M6,SUM(case [MONTH] when '2012-07' then [POD Codes ABC] else 0 end ) M7FROM dbo.SLAMwhere [pod codes abc] = 'HAA3040D' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-02 : 15:40:09
|
Thanks Visakh, for that.To add some color to the change that Visakh made:-- This would return the same count regardless of whether MONTH is 2012-04 or not, -- because count is simply counting the NON-NULLSCOUNT (case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4-- You can make that work using COUNT if you remove the else clauseCOUNT (case [MONTH] when '2012-04' then [POD Codes ABC] end ) M4-- But that has the often annoying message that says-- Warning: Null value is eliminated by an aggregate or other SET operation.-- using the SUM as shown in Visakh's query eliminates that annoyanceSUM(case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4 |
|
|
andypgill
Starting Member
34 Posts |
Posted - 2013-02-04 : 05:39:55
|
quote: Originally posted by visakh16 should beSELECT [POD Codes ABC], SUM(case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4,SUM(case [MONTH] when '2012-05' then [POD Codes ABC] else 0 end ) M5,SUM(case [MONTH] when '2012-06' then [POD Codes ABC] else 0 end ) M6,SUM(case [MONTH] when '2012-07' then [POD Codes ABC] else 0 end ) M7FROM dbo.SLAMwhere [pod codes abc] = 'HAA3040D'group by [POD Codes ABC] if you're looking only for a particular code like HAA3040D then no need of GROUP BYjust do SELECT SUM(case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4,SUM(case [MONTH] when '2012-05' then [POD Codes ABC] else 0 end ) M5,SUM(case [MONTH] when '2012-06' then [POD Codes ABC] else 0 end ) M6,SUM(case [MONTH] when '2012-07' then [POD Codes ABC] else 0 end ) M7FROM dbo.SLAMwhere [pod codes abc] = 'HAA3040D' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Thanks for the reply however I get an error "Conversion failed when converting the nvarchar value 'HAA3040D' to data type int."Should I be using sum as I want to count the number of times for each [POD Codes ABC], which in this example is 'HAA3040D' |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-04 : 05:42:38
|
Is [pod codes abc] integer type? May be this?SELECT [POD Codes ABC], SUM(case [MONTH] when '2012-04' then 1 else 0 end ) M4,SUM(case [MONTH] when '2012-05' then 1 else 0 end ) M5,SUM(case [MONTH] when '2012-06' then 1 else 0 end ) M6,SUM(case [MONTH] when '2012-07' then 1 else 0 end ) M7FROM dbo.SLAMwhere [pod codes abc] = 'HAA3040D'group by [POD Codes ABC] |
|
|
andypgill
Starting Member
34 Posts |
Posted - 2013-02-04 : 05:48:37
|
quote: Originally posted by James K Is [pod codes abc] integer type? May be this?SELECT [POD Codes ABC], SUM(case [MONTH] when '2012-04' then 1 else 0 end ) M4,SUM(case [MONTH] when '2012-05' then 1 else 0 end ) M5,SUM(case [MONTH] when '2012-06' then 1 else 0 end ) M6,SUM(case [MONTH] when '2012-07' then 1 else 0 end ) M7FROM dbo.SLAMwhere [pod codes abc] = 'HAA3040D'group by [POD Codes ABC]
Brilliant, thanks for your help.Out of interest [pod codes abc] is nvarchar(255)Andy |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-04 : 05:55:12
|
Now that I think about it, when you change your query from using COUNT to using a SUM, you have to use 1 instead of the column even if the data type is integer or other numeric type. My mistake! Sorry about that, but glad it worked out for you in the end. |
|
|
|