How can I summarize data by year in SQL?

Question

I'm sure the request is rather straight-forward, but I'm stuck. I'd like to take the first table below and turn it into the second table by summing up Incremental_Inventory by Year.

+-------------+-----------+----------------------+-----+
|Warehouse_ID |Date       |Incremental_Inventory |Year |
+-------------+-----------+----------------------+-----+
|            1|03/01/2010 |125                   |2010 |
|            1|08/01/2010 |025                   |2010 |
|            1|02/01/2011 |150                   |2011 |
|            1|03/01/2011 |200                   |2011 |
|            2|03/01/2012 |125                   |2012 |
|            2|03/01/2012 |025                   |2012 |
+-------------+-----------+----------------------+-----+

to

+-------------+-----------+---------------------------+
|Warehouse_ID |Date       |Cumulative_Yearly_Inventory|
+-------------+-----------+---------------------------+
|            1|03/01/2010 |125                        |
|            1|08/01/2010 |150                        |
|            1|02/01/2011 |150                        |
|            1|03/01/2011 |350                        |
|            2|03/01/2012 |125                        |
|            2|03/01/2012 |150                        |
+-------------+-----------+---------------------------+

We need more info-- Is is>>Summing Up by WAREHOUSE, by Year >>OR<< Summing Up by Year, by Warehouse?? What are the expected results when there is 400 Inventory for Warehouse#2 for 04/01/2011 ? — donPablo, Aug 20 '18 at 20:11
Do something like this SUM(Incremental_Inventory ) OVER (PARTITION BY Year ORDER BY Year ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) — SqlKindaGuy, Aug 20 '18 at 20:12
Why would you want to do that using PROC SQL? It is much easier to do using normal SAS programming statements. — Tom, Aug 21 '18 at 05:18

sticky bit · Answer 1 · 2018-08-20T20:14:12.627

If your DBMS, which you haven't told us, supports window functions you could simply do something like:

SELECT warehouse_id,
       date,
       sum(incremental_inventory) OVER (PARTITION BY warehouse_id,
                                                     year(date)
                                        ORDER BY date) cumulative_yearly_inventory
       FROM elbat
       ORDER BY date;

year() maybe needs to replaced by the means your DBMS provides to extract the year from a date.

If it doesn't support window functions you had to use a subquery and aggregation.

SELECT t1.warehouse_id,
       t1.date,
       (SELECT sum(t2.incremental_inventory)
               FROM elbat t2
               WHERE t2.warehouse_id = t1.warehouse_id
                     AND year(t2.date) = year(t1.date)
                     AND t2.date <= t1.date) cumulative_yearly_inventory
       FROM elbat t1
       ORDER BY t1.date;

However, if there are two equal dates, this will print the same sum for both of them. One would need another, distinct column to sort that out and as far as I can see you don't have such a column in the table.

I'm not sure if you want the sum over all warehouses or only per warehouse. If you don't want the sums split by warehouses but one sum for all warehouses together, remove the respective expressions from the PARTITION BY or inner WHERE clause.

I'm working in SAS EG via Proc Sql. Does this support window functions? Do you have another suggestion? — tubaguy, Aug 20 '18 at 19:52
@tubaguy: I extended the answer. But sorry, I don't know about the support of window functions in the product you're using. Maybe just try it or have a look at the manual. — sticky bit, Aug 20 '18 at 20:00

score 0 · Accepted Answer · answered Aug 20 '18 at 22:15

If you have SAS/ETS then the time series tasks will do this for you. Assuming not, here's a data step solution.

Use RETAIN to hold value across rows

Use BY to identify the first record for each year

data want;
set have;
by year;
retain cum_total;

if first.year then cum_total=incremental_inventory;
else cum_total+incremental_inventory;
run;

How can I summarize data by year in SQL?

2 Answers2