Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 basic sql question Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, July 24, 2013 1:30 PM
 Forum 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 Amount1 a 14.452 b 7.423 a 11.122 a 4.223 b 7.88The 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.423 11.12 7.88
Post #1477242
 Posted Wednesday, July 24, 2013 2:00 PM
 SSCoach Group: General Forum Members Last Login: Friday, December 2, 2016 6:23 AM Points: 15,499, Visits: 13,162
 Welcome to the forum!Something like this maybe?`WITH CTE_ID AS -- get list of IDs(SELECT DISTINCT ID FROM MyTable)SELECT IDFROM CTE_IDLEFT OUTER JOIN (SELECT ID, SUM(Amount) FROM MyTable WHERE Type = 'a' GROUP BY ID) ValuesA ON CTE_ID.ID = ValuesA.IDLEFT 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 SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1477253
 Posted Wednesday, July 24, 2013 2:45 PM
 SSCrazy Eights Group: General Forum Members Last Login: Friday, December 2, 2016 7:14 PM Points: 8,200, Visits: 17,740
 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_BFROM MyTableGROUP BY ID ORDER BY ID`For more information, you can check Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Luis C.General Disclaimer: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?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1477276
 Posted Wednesday, July 24, 2013 2:49 PM
 Forum 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 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_BFROM MyTableGROUP BY ID ORDER BY ID`For more information, you can check Cross Tabs and Pivots, Part 1 – Converting Rows to ColumnsThanks, 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 TotalFROM MyTableGROUP 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
 SSCrazy Eights Group: General Forum Members Last Login: Friday, December 2, 2016 7:14 PM Points: 8,200, Visits: 17,740
 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.General Disclaimer: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?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1477300
 Posted Thursday, July 25, 2013 1:35 AM
 SSCoach Group: General Forum Members Last Login: Friday, December 2, 2016 6:23 AM Points: 15,499, Visits: 13,162
 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_BFROM MyTableGROUP BY ID ORDER BY ID`For more information, you can check Cross Tabs and Pivots, Part 1 – Converting Rows to ColumnsThat'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 SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1477360
 Posted Thursday, July 25, 2013 5:36 AM
 SSCrazy Eights Group: General Forum Members Last Login: Friday, December 2, 2016 7:14 PM Points: 8,200, Visits: 17,740
 To avoid the zero and have nulls, you just need to eliminate the else. Luis C.General Disclaimer: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?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1477442
 Posted Thursday, July 25, 2013 5:43 AM
 SSCoach Group: General Forum Members Last Login: Friday, December 2, 2016 6:23 AM Points: 15,499, Visits: 13,162
 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 SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1477443

 Permissions