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?
x
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