GROUP BY, then COMPUTE

Question

Post reply

GROUP BY, then COMPUTE

Seggerman-675349

Hall of Fame

Points: 3563
More actions
December 22, 2008 at 11:30 am

#199175

is it possible to add a COMPUTE statement to roll up a GROUP BY total?
OR is there another way (i.e. WITH ROLLUP)
SELECT ID, SUM(Amount) AS Amount
FROM tblTable GROUP BY ID
COMPUTE SUM(Amount)
doesn't work
thanks

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 1

let's say the data is like this:

ID 1 Amount 10.00

ID 1 Amount 20.00

ID 2 Amount 10.00

then the results I want is

ID Amount

1 30.00

2 10.00

sum

40.00

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

Are you looking for something like this?

--create test table

CREATE TABLE #test (

id INT,

amount MONEY)

--insert sample data

INSERT INTO [#test]

SELECT 1,10.00

UNION ALL

SELECT 1,20.00

UNION ALL

SELECT 2,10.00

SELECT ID, SUM(Amount) AS Amount

FROM [#test]

GROUP BY [ID] WITH Rollup

--cleanup

DROP TABLE [#test]

To help us help you read this[/url]For better help with performance problems please read this[/url]

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 3

Seggerman-675349

Hall of Fame

Points: 3563

December 22, 2008 at 2:23 pm

#916410

yup, that does the trick

thanks

Marianne

Jeff Moden SSC Guru Points: 1004593 More actions · Answer 4

Using Luke's good code example, you can label to "sum", as well...

--create test table

CREATE TABLE #test (

id INT,

amount MONEY)

--insert sample data

INSERT INTO [#test]

SELECT 1,10.00

UNION ALL

SELECT 1,20.00

UNION ALL

SELECT 2,10.00

SELECT CASE WHEN GROUPING(ID) = 0 THEN STR(ID,10) ELSE 'Sum Total' END AS ID, SUM(Amount) AS Amount

FROM [#test]

GROUP BY [ID] WITH Rollup

--cleanup

DROP TABLE [#test]

Of course, everyone will tell you that should be done in the GUI... except maybe for me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

Thanks Jeff, I know you explained the logic to add the Sum bit for the total line, but I didn't have time to identify which one of your articles it was in, as I was running out the door yesterday when I wrote this. If you could post a link that'd be very helpful.

Thanks.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Jeff Moden SSC Guru Points: 1004593 More actions · Answer 6

Luke L (12/23/2008)
Thanks Jeff, I know you explained the logic to add the Sum bit for the total line, but I didn't have time to identify which one of your articles it was in, as I was running out the door yesterday when I wrote this. If you could post a link that'd be very helpful.
Thanks.
-Luke.

Absolutely no problem. I gave it just a casual mention in one of the CrossTab articles. The GROUPING is much better explained in Books Online if you lookup "GROUP BY clause, GROUPING function"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GROUP BY, then COMPUTE

Cookies on SQLServerCentral