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 |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2005-12-28 : 23:13:12
|
I've written several stored procs that use ROLLUP. It's late, maybe I'm tired, but I need help...SELECT Username, Organization, Value1, Value2, Value1+Value2Results in a record set showing all user names, organizations, along with 3 Integer columns.I'd like to provide Subtotals, by organization which display averages of Value1, value2 and Value1+Value2A Grand Total average on the last row too.ROLLUP only works with aggregate functions. What am I missing? |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2005-12-28 : 23:21:20
|
| I think you want something like "compute avg(value1),avg(value2),avg(value1+value2) by organization". Not sure if you can use both "compute by" and "compute" in the same statement to get your totals, though.Cheers-b |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-12-29 : 00:19:36
|
| I'm calling it a night.I think the solution is to do a separate GROUP BY recordset and UNION it with the first. Additional sorting fields/columns will need to be added...... Query aboveUNION ALLSELECT Organization, AVE(Value1), AVE(Value2), AVE(Value1+Value2)...GROUP BY Organization WITH ROLLUP |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2005-12-29 : 15:32:37
|
| You sure compute by won't work? Seems like just what it's for.-b |
|
|
|
|
|
|
|
|
I thought I knew ROLLUP