Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

basic sql question Expand / Collapse
Author
Message
Posted Wednesday, July 24, 2013 1:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 6:58 PM
Points: 3, Visits: 9
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):

ID Type Amount
1 a 14.45
2 b 7.42
3 a 11.12
2 a 4.22
3 b 7.88


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:

ID Amount where type is "a" Amount where type is "b"
1 14.45
2 4.42 7.42
3 11.12 7.88

Post #1477242
Posted Wednesday, July 24, 2013 2:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 13,529, Visits: 11,326
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1477253
Posted Wednesday, July 24, 2013 2:45 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 3,908, Visits: 8,867
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



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1477276
Posted Wednesday, July 24, 2013 2:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 6:58 PM
Points: 3, Visits: 9
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!!
Post #1477279
Posted Wednesday, July 24, 2013 3:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 6:58 PM
Points: 3, Visits: 9
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


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!
Post #1477295
Posted Wednesday, July 24, 2013 3:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 3,908, Visits: 8,867
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/



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1477300
Posted Thursday, July 25, 2013 1:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 13,529, Visits: 11,326
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


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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1477360
Posted Thursday, July 25, 2013 5:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 3,908, Visits: 8,867
To avoid the zero and have nulls, you just need to eliminate the else.


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1477442
Posted Thursday, July 25, 2013 5:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 13,529, Visits: 11,326
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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1477443
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse