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