Post reply

GROUP BY, then COMPUTE

  • 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

    🙂

  • December 22, 2008 at 11:45 am

    #916295

    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

  • December 22, 2008 at 2:12 pm

    #916404

    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]

  • December 22, 2008 at 2:23 pm

    #916410

    yup, that does the trick

    thanks

    Marianne

    😛

  • December 22, 2008 at 4:38 pm

    #916470

    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)

  • December 23, 2008 at 6:07 am

    #916723

    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]

  • December 23, 2008 at 6:16 am

    #916733

    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)

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

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