How to write a query to show sum of a column value and show beside that?

Question

I have a table with 4 columns Id, Date, Material, Amount.

Sample data looks like this :

--------------------------------------------
   Id    |    Date    | Material|   Amount  
--------------------------------------------
   1     | 01/01/2013 |  1      |    500   
   2     | 01/01/2013 |  2      |    400  
   3     | 01/01/2013 |  1      |    200  
   4     | 02/01/2013 |  1      |    600    
   5     | 02/01/2013 |  2      |    200   
   6     | 03/01/2013 |  2      |    100    
   7     | 04/01/2013 |  1      |    800     
   8     | 05/01/2013 |  2      |    400   
   9     | 06/01/2013 |  1      |    800     
   10    | 06/01/2013 |  2      |    700   
   11    | 06/01/2013 |  1      |    600

I need to write a query to get the result like this :

-----------------------------------------------
    Date    | Material  |   DailyAmount | Total 
-----------------------------------------------
 01/01/2013 |    1      |    700        |  700
 02/01/2013 |    1      |    400        |  1100
 04/01/2013 |    1      |    800        |  1900
 06/01/2013 |    1      |    1400       |  3300

Dailyamount is sum of Amount in a date. I just want to select Material = 1. the Total field is sum of DailyAmount until that day. I searched much, but I can't get anything about this ...

How do you select date? please explain your output. – Sateesh Pagolu Oct 02 '15 at 12:51 — Sateesh Pagolu, Oct 02 '15 at 12:51
I just add some details @DarkKnight – Mohammad Khandordi Oct 02 '15 at 12:59 — Mohammad Khandordi, Oct 02 '15 at 12:59
@Ullas because material =1 does not exist in that date... – Mohammad Khandordi Oct 02 '15 at 13:01 — Mohammad Khandordi, Oct 02 '15 at 13:01
I got it. That's why deleted that comment – Ullas Oct 02 '15 at 13:01 — Ullas, Oct 02 '15 at 13:01

Mureinik · Answer 1 · 2015-10-02T13:13:08.253

1

You can use the analytic (window) variant of the sum function to get the running total. The per-date sum should be done with a regular, aggregate, sum):

SELECT [date], [material], [DailyAmount], 
       SUM([DailyAmount]) OVER (ORDER BY [date]
                                ROWS BETWEEN UNBOUNDED PRECEDING AND 
                                             CURRENT ROW) AS [Total]
FROM   (SELECT   [date], material, SUM([amount] AS [DailyAmount]
        FROM     my_table
        WHERE    material = 1
        GROUP BY [date], material) t

edited Oct 02 '15 at 13:13

answered Oct 02 '15 at 13:05

Mureinik

297,002
52
306
350

This doesn't get the `sum` of `amount` for `DailyAmount` – HoneyBadger Oct 02 '15 at 13:10
@HoneyBadger I completely skipped that phase, how embarrassing. Edited and fixed. – Mureinik Oct 02 '15 at 13:13

Sateesh Pagolu · Answer 2 · 2015-10-02T13:36:36.677

0

SQL Fiddle Demo

WITH CTE AS
(
    SELECT DATE1,MATERIAL,SUM(AMOUNT) AS AMOUNT
    FROM DATA
    GROUP BY DATE1,MATERIAL
)
SELECT DATE1,MATERIAL,SUM(AMOUNT) OVER(ORDER BY DATE1)
FROM CTE
WHERE MATERIAL=1

Other way of doing it..

WITH CTE AS
(
    SELECT DATE1,MATERIAL,SUM(AMOUNT) AS AMOUNT ,ROW_NUMBER() OVER(PARTITION BY MATERIAL ORDER BY DATE1) AS RN
    FROM DATA
    GROUP BY DATE1,MATERIAL
),
 CTE1 AS
(
    SELECT DATE1,MATERIAL,AMOUNT,RN FROM CTE WHERE RN=1
    UNION ALL
    SELECT  C2.DATE1,C2.MATERIAL,C1.AMOUNT+C2.AMOUNT,C2.RN 
    FROM CTE1 C1
            INNER JOIN CTE C2 ON C1.RN+1 = C2.RN AND C1.MATERIAL = C2.MATERIAL          
)
SELECT * FROM CTE1 ORDER BY MATERIAL,DATE1

edited Oct 02 '15 at 13:36

answered Oct 02 '15 at 13:06

Sateesh Pagolu

9,282
2
30
48

Just as a side note, while this should be good enough to help OP, it doesn't get the value for `DailyAmount` – HoneyBadger Oct 02 '15 at 13:12
could it be without OVER syntax... with simple syntax – Mohammad Khandordi Oct 02 '15 at 13:21
@MohammadKhandordi : Either `ROW_NUMBER() OVER` or `SUM() OVER` is needed. – Sateesh Pagolu Oct 02 '15 at 13:25

score 0 · Answer 3 · answered Oct 02 '15 at 13:33

Can achieve this with a CTE and a CROSS JOIN.

Query

;with cte as 
(
   select rn = row_number() over
   (
      order by [date]
   ),[Date],Material,sum(Amount) as Amount
   from myTable
   where Material = 1
   group by [Date],Material
)
select t1.[Date],t1.Material,t1.Amount as DailyAmount,
sum(t2.Amount) as Total
from cte t1 
cross join cte t2
where t2.rn <= t1.rn
group by t1.[Date],t1.Material,t1.Amount;

SQL Fiddle

score 0 · Answer 4 · answered Oct 02 '15 at 14:09

 ;WITH CTE AS
   (
    SELECT
      [Date]   
     ,Material  
     ,SUM(Amount)  DailyAmount

   FROM
   YourTable
   WHERE 
   Material = 1
   GROUP BY
    [Date]    
   ,Material  
    )

    SELECT 
     A.[Date]
    ,A.Material
    ,A.DailyAmount
    ,SUM(B.DailyAmount)
    FROM CTE A
    INNER JOIN
    CTE B
    ON
    B.[Date]<=A.[Date]
    GROUP BY
     A.[Date]
    ,A.Material
    ,A.DailyAmount

How to write a query to show sum of a column value and show beside that?

4 Answers4