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 |
|
melissar
Starting Member
15 Posts |
 Posted - 2003-06-13 : 15:34:34
|
| I know that I saw a way to set a value for the total rows (in place of null) of the rollup results in the forum, but I have been searching for hours and can't seem to locate. Could someone please show me how to place text in the total row of a rollup query?Thanks! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-13 : 19:56:58
|
| Something like:SELECT IsNull(Name, 'All') AS Name, Sum(Amount) AS TotalFROM SalesGROUP BY NAME WITH ROLLUPORDER BY GROUPING(Name), Name...would work, but this is the preferred method:SELECT CASE GROUPING(Name) WHEN 0 THEN Name ELSE 'All' END AS Name, Sum(Amount) AS TotalFROM SalesGROUP BY NAME WITH ROLLUPORDER BY GROUPING(Name), NameThe reason is, if you have actual Null's in your results, the IsNull() formula would replace them with "All" even though they don't represent a summary row. The CASE...GROUPING will only replace values for the summary rows. |
 |
|
|
melissar
Starting Member
15 Posts |
Posted - 2003-06-16 : 10:18:34
|
| Awesome. Thanks! |
|
|
|
|
|
|
Rollup Totals