July 24, 2013 at 1:30 pm
Hello! First time post here... hoping for help with what is probably a very, very simple question....
I have a table of statistics that looks sort of like this (very simplified):
[font="Courier New"]ID Type Amount
1 a 14.45
2 b 7.42
3 a 11.12
2 a 4.22
3 b 7.88[/font]
The IDs represent people. The Type is always either an 'a' or else a 'b'. Each person has optionally one a entry, and optionally one b entry.
What query could give me a result set like so:
[font="Courier New"]ID Amount where type is "a" Amount where type is "b"
1 14.45
2 4.42 7.42
3 11.12 7.88[/font]
July 24, 2013 at 2:00 pm
Welcome to the forum!
Something like this maybe?
WITH CTE_ID AS -- get list of IDs
(SELECT DISTINCT ID FROM MyTable)
SELECT
ID
FROM CTE_ID
LEFT OUTER JOIN
(SELECT ID, SUM(Amount) FROM MyTable WHERE Type = 'a' GROUP BY ID) ValuesA ON CTE_ID.ID = ValuesA.ID
LEFT OUTER JOIN
(SELECT ID, SUM(Amount) FROM MyTable WHERE Type = 'b' GROUP BY ID) ValuesB ON CTE_ID.ID = ValuesB.ID;
I first get a list of all the IDs, so I can still show a row when the value for a or b is missing.
On a sidenote: maybe for your next question, you can try to follow directions listed in the first link of my signature on how to post questions. Posting table DDL, sample data et cetera.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 24, 2013 at 2:45 pm
I believe this option is better for performance.
SELECT ID,
SUM( CASE WHEN Type = 'a' THEN Amount ELSE 0 END) AS Amount_A,
SUM( CASE WHEN Type = 'b' THEN Amount ELSE 0 END) AS Amount_B
FROM MyTable
GROUP BY ID
ORDER BY ID
For more information, you can check Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
July 24, 2013 at 2:49 pm
Koen Verbeeck (7/24/2013)
Welcome to the forum!
Thank you for your quick answer, which definitely made me feel welcome. And even better, appears to work.
On a sidenote: maybe for your next question, you can try to follow directions listed in the first link of my signature on how to post questions. Posting table DDL, sample data et cetera.
Ah-hah... also very helpful. Roger Wilco, and thanks again!!
July 24, 2013 at 3:32 pm
Luis Cazares (7/24/2013)
I believe this option is better for performance.
SELECT ID,SUM( CASE WHEN Type = 'a' THEN Amount ELSE 0 END) AS Amount_A,
SUM( CASE WHEN Type = 'b' THEN Amount ELSE 0 END) AS Amount_B
FROM MyTable
GROUP BY ID
ORDER BY ID
For more information, you can check Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
Thanks, Luis! That link you sent is the best definition of "CrossTabs" and "PivotTables" I've ever seen. I always wondered what those actually were.
And yes... this syntax works very fast... I forgot to set a rowcount, and it scrolled out 42,000 results almost instantly. Cool!
Plus, it is easy to add a "Total" column and sort by that instead.
SELECT ID,
SUM( CASE WHEN Type = 'a' THEN Amount ELSE 0 END) AS Amount_A,
SUM( CASE WHEN Type = 'b' THEN Amount ELSE 0 END) AS Amount_B,
SUM( Amount) AS Total
FROM MyTable
GROUP BY ID
ORDER BY Total Desc
Plus, I like how clean and simple it is to add WHERE conditions after MyTable.
Now I just need to do some JOINS to pull in names various things... but thanks to you, I feel I have a great foundation. Thank you so much!
July 24, 2013 at 3:42 pm
I'm glad I could help.
Remember to try the pre-aggregation option to improve performance even more and you could check the second part of Cross tabs for further learning. You can find it here http://www.sqlservercentral.com/articles/Crosstab/65048/
July 25, 2013 at 1:35 am
Luis Cazares (7/24/2013)
I believe this option is better for performance.
SELECT ID,SUM( CASE WHEN Type = 'a' THEN Amount ELSE 0 END) AS Amount_A,
SUM( CASE WHEN Type = 'b' THEN Amount ELSE 0 END) AS Amount_B
FROM MyTable
GROUP BY ID
ORDER BY ID
For more information, you can check Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
That's the one I came up initially, but the example showed a NULL for lD 1, so I wanted to avoid showing any 0.
Picky picky 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 25, 2013 at 5:36 am
To avoid the zero and have nulls, you just need to eliminate the else.
July 25, 2013 at 5:43 am
Luis Cazares (7/25/2013)
To avoid the zero and have nulls, you just need to eliminate the else.
Well, I'm glad someone is still awake 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply