Skip to content
Advertisement

Aggregate quantity columns per distinct date in table sql

I want to sum quantity column from first date in table (2016-02-17 in this table) until per each distinct date in the table. Result relation should contains sum of quantities per each distinct date in table. how can I write a query for this in sql server?

 ID| quantity | date
---+----------+-----
18 |     6    | 2016-02-17 00:00:00.000
19 |     6    | 2016-02-17 00:00:00.000
18 |     4    | 2016-02-17 00:00:00.000
19 |     3    | 2016-02-18 00:00:00.000
18 |     1    | 2016-02-18 00:00:00.000
19 |     5    | 2016-02-18 00:00:00.000
18 |     6    | 2016-02-19 00:00:00.000
19 |     7    | 2016-02-19 00:00:00.000
18 |     8    | 2016-02-19 00:00:00.000
19 |     9    | 2016-02-19 00:00:00.000

Expected output:

|       Date | quantity |
|------------|----------|
| 2016-02-17 |       16 |
| 2016-02-18 |       25 |
| 2016-02-19 |       55 |

Advertisement

Answer

Aggregate function SUM with GROUP BY will give you the sum values for the Distinct dates.

SELECT Date, 
    SUM(quantity) OVER(ORDER BY Date) quantity
FROM(
    SELECT DATE, SUM(quantity) quantity
    FROM Your_Table 
    GROUP BY DATE
    )A

Check the SQL Fiddle for reference.

If you want the result for ID specific, use this. The PARTITION will make the difference.

SELECT Id, Date,
    SUM(quantity) OVER(PARTITION BY ID ORDER BY Date) quantity
FROM(
    SELECT Id, DATE, SUM(quantity) quantity
    FROM A 
    GROUP BY Id, DATE
    )A
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement