Displaying totals nicely

Question

Displaying totals nicely

DiStas

SSC Enthusiast

Points: 149
More actions
March 17, 2008 at 12:47 am

#188054

There is table PRODUCTS:
PROD_ID PLACE PRICE
1 AAA 10
1 BBB 15
1 CCC 20
2 AAA 30
2 BBB 40
3 AAA 50
I want to get sum(PRICE) grouped by PROD_ID.
With this code:
SELECT
PROD_ID,
PLACE,
PRICE,
(SELECT SUM(PRICE) FROM PRODUCTS WHERE PROD_ID=P.PROD_ID) TOTAL_PRICE
FROM PRODUCTS P
GROUP BY PROD_ID, PLACE, PRICE
results are displayed as:
PROD_ID PLACE PRICE TOTAL_PRICE
1 AAA 10 45
1 BBB 15 45
1 CCC 20 45
2 AAA 30 70
2 BBB 40 70
3 AAA 50 50
What I want something li this:
PROD_ID PLACE PRICE TOTAL_PRICE
1 AAA 10
1 BBB 15
1 CCC 20
45
2 AAA 30
2 BBB 40
70
3 AAA 50 50
50
Can it be implemented with plain SQL?

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

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

"45" , "70" will appear in all the records that are returned. The display part as how you want it will have to be done in the Front-End/UI level.

You can return one recordset for the data and another recordset for the total. But the display part would still be done in the Front-End/UI level.

You can do some T-SQL coding and use temp tables to do one thing. "45" , "70" can be updated only for the last row of a particular PROD_ID .

Hope this helps

"Keep Trying"

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 2

I haven't really used them myself, but I'm wondering if this is a case to use the "with cube" or "with rollup" features. Can anyone else provide some more insight into those?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

WITH ROLLUP would do the trick only if the intermediate results were also sums. Without it - it's actually going to be easier to do that using a UNION ALL Syntax:

select Prod_ID, price, totalprice

from

(select

Prod_ID,

0 as groupinglev,

PRICE,

null as totalPRICE

from PRODUCTS

UNION ALL

selectProd_ID,

1 as groupinglev,

null as PRICE,

sum(price) as totalPRICE

from PRODUCTS

group by Prod_ID

) s

order by Prod_ID,groupinglev

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

KarenD SSC Veteran Points: 260 More actions · Answer 4

If you have SQL Server 2005 then use a common table expression (CTE).

It should be faster for large amounts of data.

WITH get_products AS

( SELECT PROD_ID, PLACE, PRICE FROM

[Test1].[dbo].[PRODUCTS])

SELECT PROD_ID, PLACE, PRICE , NULL AS total_price

FROM get_products

UNION ALL

SELECT PROD_ID,NULL place ,NULL AS price, SUM(price) AS total_price

FROM get_products

GROUP BY PROD_ID

ORDER BY PROD_ID , total_PRICE , PLACE

Thanks for the nifty union all solution; I used it as the basis for this.

DiStas SSC Enthusiast Points: 149 More actions · Answer 5

Thank you, Matt and Karen for your suggestions. It helped. CTE solution is really neat!

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

For your next post... read the article at the URL in my signature line.

--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)