June 26, 2015 at 1:24 pm
Hello all,
new to the forum so if this is the incorrect place, I apologize. New to SQL and having issues with getting my data the way I want it.
This is my query;
Select distinct
a.I3_ROWID As CAMPAIGN,
Count(Case When a.Blood_Type In ('A -') Then 1 End) As [A-],
Count(Case When a.Blood_Type In ('A +') Then 1 End) As [A+],
Count(Case When a.Blood_Type In ('B -') Then 1 End) As [B-],
Count(Case When a.Blood_Type In ('B +') Then 1 End) As [B+],
Count(Case When a.Blood_Type In ('O -') Then 1 End) As [O-],
Count(Case When a.Blood_Type In ('O +') Then 1 End) As [O+],
Count(Case When a.Blood_Type In ('AB -') Then 1 End) As [AB-],
Count(Case When a.Blood_Type In ('AB +') Then 1 End) As [AB+],
Count(Case
When a.Blood_Type Not In ('A -', 'A +', 'B -', 'B +', 'O -', 'O +', 'AB -',
'AB +') Then 1 End) As Other
From
[CALL_LIST-BHQ_GROUP3] a With(NoLock)
Group By
a.I3_ROWID, a.Blood_Type
Attached is my result set.
Also attached is my intended result set.
How do I get my intended result set?
Thanks in advance!
June 26, 2015 at 1:41 pm
nick.johnson 39251 (6/26/2015)
Hello all,new to the forum so if this is the incorrect place, I apologize. New to SQL and having issues with getting my data the way I want it.
This is my query;
Select distinct
a.I3_ROWID As CAMPAIGN,
Count(Case When a.Blood_Type In ('A -') Then 1 End) As [A-],
Count(Case When a.Blood_Type In ('A +') Then 1 End) As [A+],
Count(Case When a.Blood_Type In ('B -') Then 1 End) As [B-],
Count(Case When a.Blood_Type In ('B +') Then 1 End) As [B+],
Count(Case When a.Blood_Type In ('O -') Then 1 End) As [O-],
Count(Case When a.Blood_Type In ('O +') Then 1 End) As [O+],
Count(Case When a.Blood_Type In ('AB -') Then 1 End) As [AB-],
Count(Case When a.Blood_Type In ('AB +') Then 1 End) As [AB+],
Count(Case
When a.Blood_Type Not In ('A -', 'A +', 'B -', 'B +', 'O -', 'O +', 'AB -',
'AB +') Then 1 End) As Other
From
[CALL_LIST-BHQ_GROUP3] a With(NoLock)
Group By
a.I3_ROWID, a.Blood_Type
Attached is my result set.
Also attached is my intended result set.
How do I get my intended result set?
Thanks in advance!
It's the GROUP BY, for the most part. I would also not rely on NULL behavior, for your COUNT functions, and would use SUM instead. I also took out the NOLOCK hint, as that's a good way to get bad data. Here's the updated query:
SELECT a.I3_ROWID AS CAMPAIGN,
SUM(CASE WHEN a.Blood_Type IN ('A -') THEN 1 ELSE 0 END) AS [A-],
SUM(CASE WHEN a.Blood_Type IN ('A +') THEN 1 ELSE 0 END) AS [A+],
SUM(CASE WHEN a.Blood_Type IN ('B -') THEN 1 ELSE 0 END) AS [B-],
SUM(CASE WHEN a.Blood_Type IN ('B +') THEN 1 ELSE 0 END) AS [B+],
SUM(CASE WHEN a.Blood_Type IN ('O -') THEN 1 ELSE 0 END) AS [O-],
SUM(CASE WHEN a.Blood_Type IN ('O +') THEN 1 ELSE 0 END) AS [O+],
SUM(CASE WHEN a.Blood_Type IN ('AB -') THEN 1 ELSE 0 END) AS [AB-],
SUM(CASE WHEN a.Blood_Type IN ('AB +') THEN 1 ELSE 0 END) AS [AB+],
SUM(CASE WHEN a.Blood_Type NOT IN ('A -', 'A +', 'B -', 'B +', 'O -', 'O +', 'AB -', 'AB +') THEN 1 ELSE 0 END) AS Other
FROM [CALL_LIST-BHQ_GROUP3] AS a
GROUP BY a.I3_ROWID
Let me know how that works for you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 26, 2015 at 2:52 pm
Since you say you are new to SQL I would ask if you understand what that NOLOCK is really doing. It can and will get missing and/or duplicate rows. It is not a magic "go fast button" that doesn't have some potentially serious implications. Here are some great articles about this topic. I am not saying you shouldn't be using this hint. I am saying you need to be 100% certain of what it does when using it.
http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/%5B/url%5D
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply