Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: Put SUM in the last row

    Hello everyone:

    I have a detail table like,
    Cust_Num Order_Num Prod_Num QTY Unit_Price Total
    26389850 84662965 7273 1 26.99 NULL
    26389850 84662965 6582 1 31.39 NULL
    26389850 84662965 131 1 19.55 NULL
    26389850 84662965 5141 1 8.89 NULL
    26389850 84662965 7903 1 4.99 NULL
    26389850 84662965 2180 1 6.35 NULL
    26389860 84662975 7743 1 9.49 NULL
    26389860 84662975 5745 1 12.99 NULL
    26389860 84662975 78634 1 29.99 NULL
    26389860 84662975 3640 1 4.65 NULL
    26389860 84662975 3650 1 3.5 NULL
    26389860 84662975 1840 1 8.95 NULL

    I want to put SUM of unit_price in the last row for each order_num, like,
    Cust_Num Order_Num Prod_Num QTY Unit_Price Total
    26389850 84662965 7273 1 26.99 NULL
    26389850 84662965 6582 1 31.39 NULL
    26389850 84662965 131 1 19.55 NULL
    26389850 84662965 5141 1 8.89 NULL
    26389850 84662965 7903 1 4.99 NULL
    26389850 84662965 2180 1 6.35 98.16
    26389860 84662975 7743 1 9.49 NULL
    26389860 84662975 5745 1 12.99 NULL
    26389860 84662975 78634 1 29.99 NULL
    26389860 84662975 3640 1 4.65 NULL
    26389860 84662975 3650 1 3.5 NULL
    26389860 84662975 1840 1 8.95 69.57

    Any suggest is appreciated.

    ZYT

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The easiest method would be to use an application language, such as JAVA, .NET, C#, and what other languages exist. If you want to do it in the database, you can just a sub-query and a union statement.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    look up GROUP BY WITH ROLLUP
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    another way would be to use WITH ROLLUP in your query

    the tricky part is deciding what goes in the GROUP BY

    hint: i bet you don't really want the SUM of unit price, but rather the SUM of quantity times unit price

    your example shows only Qty=1, by the way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    another way would be to use WITH ROLLUP
    [Guiness commerical]

    Brilliant!

    [/Guiness commerical]
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •