| Author |
 Topic  |
|
|
andypgill
Starting Member
32 Posts |
 Posted - 02/01/2013 : 10:48:53
|
Hi All
Can 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 ) M7
FROM dbo.SLAM
where [pod codes abc] = 'HAA3040D'
group by [POD Codes ABC],[month]
This gives me
POD Codes ABC M4 M5 M6 M7
HAA3040D 343 0 0 0
HAA3040D 0 353 0 0
HAA3040D 0 0 86 0
HAA3040D 0 0 0 1
How do I write the code so it gives me one line per [POD Codes ABC]
eg
POD Codes ABC M4 M5 M6 M7
HAA3040D 343 353 86 1
Thanks
|
|
|
Grifter
Posting Yak Master
214 Posts |
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 02/01/2013 : 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
India
47173 Posts |
Posted - 02/02/2013 : 03:14:30
|
should be
SELECT [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 ) M7
FROM dbo.SLAM
where [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 BY
just 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 ) M7
FROM dbo.SLAM
where [pod codes abc] = 'HAA3040D'
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 02/02/2013 : 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-NULLS
COUNT (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 clause
COUNT (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 annoyance
SUM(case [MONTH] when '2012-04' then [POD Codes ABC] else 0 end ) M4 |
|
|
|
andypgill
Starting Member
32 Posts |
Posted - 02/04/2013 : 05:39:55
|
quote:
Originally posted by visakh16
should be
SELECT [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 ) M7
FROM dbo.SLAM
where [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 BY
just 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 ) M7
FROM dbo.SLAM
where [pod codes abc] = 'HAA3040D'
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 02/04/2013 : 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 ) M7
FROM dbo.SLAM
where [pod codes abc] = 'HAA3040D'
group by [POD Codes ABC] |
Edited by - James K on 02/04/2013 05:43:53 |
|
|
|
andypgill
Starting Member
32 Posts |
Posted - 02/04/2013 : 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 ) M7
FROM dbo.SLAM
where [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
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 02/04/2013 : 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. |
|
|
| |
Topic |
|
Pivots
