All Forums Database
nilesh bhawsar 1 post Joined 07/10
12 Jul 2010
Please help me with the sum function

I have a table called SALESAGGRT where i have two columns Product_Id , Total_Sales

in the table i have following data

Product_Id Total_Sales
100 1000
200 2000
300 3000
400 4000

I want in the separate column total sum of all the sales

for ex :-

Product_Id Total_Sales Sum(Total_Sales)
100 1000 10000
200 2000 10000
300 3000 10000
400 4000 10000

what should be the sql for generating the above result set.

Please help me.Thanks in advance.

robpaller 159 posts Joined 05/09
12 Jul 2010

You can do a Group Sum using the SUM() OVER(PARTITION BY Product_ID) window aggregate. However, your example indicates that the Total_Sales Column is a Cumulative Sum. The derivation to Sum(Total_Sales) in your example is not clear without either a quantity sold field or the existence of a SalesDetail table.

Jimm 298 posts Joined 09/07
12 Jul 2010

Select Product_Id
, Total_Sales
, D1.AllSales
From tbl
Cross Join (Select Sum(Total_Sales) As AllSales
From tbl) D1

You must sign in to leave a comment.