Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dmilam
Posting Yak Master
185 Posts |
 Posted - 2010-08-27 : 18:55:13
|
| Let's say I want to return totals for all customers from non-U.S. countries, but I want the aggregate, not a list of countries.Total Event Country----- ----- -------1234 1 Non-U.S.567 1 Non-U.S.890 1 Non-U.S.Listed three times here because "Non-U.S." is just an alias for Armenia, Azerbaijan, etc., not a true aggregate of all non-U.S. countries. How to sum up? Rollup, or pivot table? |
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2010-08-27 : 19:32:45
|
| Seems that I'm stuck at compatibility_level 80, so it looks like PIVOT is unavailable. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-28 : 01:10:14
|
So you want total column in single column?SELECT SUM(CASE WHEN Country='Non-U.S.' THEN Total ELSE 0 END) AS [Non-U.S.] FROM Table ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2010-08-31 : 14:25:21
|
| Yes; I did manage to get PIVOT to work by using tempdb, which is set to 90 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-09-01 : 14:06:45
|
quote:
Originally posted by dmilam
Yes; I did manage to get PIVOT to work by using tempdb, which is set to 90
changing compatibility level is fine. make sure you check and see if you've any old code that might get broken in 90 mode. If yes, rewrite it using new syntax before you change to 90------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2010-09-01 : 14:24:09
|
| Thanks; good to know! |
|
|
|
|
|
|
Rollup, or pivot?