March 17, 2008 at 12:47 am
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?
March 17, 2008 at 2:40 am
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"
March 17, 2008 at 7:19 am
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?
March 17, 2008 at 10:43 am
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?
March 18, 2008 at 9:10 am
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.
March 18, 2008 at 6:08 pm
Thank you, Matt and Karen for your suggestions. It helped. CTE solution is really neat!
March 18, 2008 at 8:07 pm
For your next post... read the article at the URL in my signature line.
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply