SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pivots
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andypgill
Starting Member

34 Posts

Posted - 02/01/2013 :  10:48:53  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

263 Posts

Posted - 02/01/2013 :  11:57:11  Show Profile  Reply with Quote
Hi

Sorry 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=pivot

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165475&SearchTerms=pivot

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=175001&SearchTerms=pivot
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 02/01/2013 :  17:31:33  Show Profile  Reply with Quote
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,
....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/02/2013 :  03:14:30  Show Profile  Reply with Quote
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/

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 02/02/2013 :  15:40:09  Show Profile  Reply with Quote
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
Go to Top of Page

andypgill
Starting Member

34 Posts

Posted - 02/04/2013 :  05:39:55  Show Profile  Reply with Quote
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'

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 02/04/2013 :  05:42:38  Show Profile  Reply with Quote
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
Go to Top of Page

andypgill
Starting Member

34 Posts

Posted - 02/04/2013 :  05:48:37  Show Profile  Reply with Quote
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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 02/04/2013 :  05:55:12  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000