Issue
Lately, I have been learning how to use SQL in order to process data. Normally, I would use Python for that purpose, but SQL is required for the classes and I still very much struggle with using it comfortably in more complicated scenarios.
What I want to achieve is the same result as in the following screenshot in Excel:
Behaviour in Excel, that I want to implement in SQL
The formula I used in Excel:
=SUMIF(B$2:B2;B2;C$2:C2)
Sample of the table:
> select * from orders limit 5;
+------------+---------------+---------+
| ID | clientID | tonnage |
+------------+---------------+---------+
| 2005-01-01 | 872-13-44-365 | 10 |
| 2005-01-04 | 369-43-03-176 | 2 |
| 2005-01-05 | 408-24-90-350 | 2 |
| 2005-01-10 | 944-16-93-033 | 5 |
| 2005-01-11 | 645-32-78-780 | 14 |
+------------+---------------+---------+
The implementation is supposed to return similar results as following group by query:
select
orders.clientID as ID,
sum(orders.tonnage) as Tonnage
from orders
group by orders.clientID;
That is, return how much each client have purchased, but at the same I want it to return each step of the addition as separate record.
For an instance:
Client A bought 350 in the first order and then 231 in the second one.
In such case the query would return something like this:
client A - 350 - 350 // first order
client A - 281 - 581 // second order
Example, how it would look like in Excel
I have already tried to use something like:
select
orders.clientID as ID,
sum(case when orders.clientID = <ID> then orders.tonnage end)
from orders;
But got stuck quickly, since I would need to somehow dynamically change this <ID> and store it’s value in some kind of temporary variable and I can’t really figure out how to implement such thing in SQL.
Solution
You can use window function for running sum.
In your case, use like this
select id, clientID, sum(tonnage) over (partition by clientID order by id) tonnageRunning
from orders
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=13a8c2d46b5ac22c5c120ac937bd6e7a
Answered By – doctorgu
Answer Checked By – Jay B. (BugsFixing Admin)