CODEWITHSUNDEEP
mysqlsql
Amitoz Deol
Amitoz Deol

Reputation: 442

Mysql find number of items using previous rows

I have a table with hundreds of itemname

   +--------+----------+-----------+
   | date   | itemname | itemvalue |
   +--------+----------+-----------+
   |2020-01 |    A     |    foo    |
   +--------+----------+-----------+
   |2020-01 |    A     |    foo1   |
   +--------+----------+-----------+
   |2020-02 |    B     |    bar    |
   +--------+----------+-----------+
   |2020-02 |    B     |    bar1   |
   +--------+----------+-----------+
   |2020-02 |    B     |    bar2   |
   +--------+----------+-----------+
   |2020-03 |    A     |    foo2   |
   +--------+----------+-----------+
   |2020-04 |    C     |    baz    |
   +--------+----------+-----------+
   |2020-04 |    C     |    baz1   |
   +--------+----------+-----------+
   |2020-05 |    B     |    bar3   |
   +--------+----------+-----------+
   |2020-05 |    B     |    bar4   |
   +--------+----------+-----------+

I want the output to be

   +--------+----------+-----------+
   | date   | itemname | itemcount |
   +--------+----------+-----------+
   |2020-01 |    A     |    2      |
   +--------+----------+-----------+
   |2020-02 |    B     |    3      |
   +--------+----------+-----------+
   |2020-03 |    A     |    3      | //1+2
   +--------+----------+-----------+
   |2020-04 |    C     |    2      |
   +--------+----------+-----------+
   |2020-05 |    B     |    5      | //2+3
   +--------+----------+-----------+

I tried group by date, itemname but that only gave me count per month. But the issue I'm having is how do I aggregate COUNT of itemvalue with previous month's itemvalue. For ex: in 2020-03, A value is a count of current(1)+previous months(2)

I'm using Mysql 5.7

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

In MySQL 8+, you can use window functions:

select date, itemname, sum(count(*)) over (partition by itemname order by date) 
from t
group by date, itemname;

If your data is not very large, then a reasonable solution is a correlated subquery:

select di.*,
       (select count(*) from t where t.itemname = di.itemname and t.date <= di.date) running
from (select distinct date, itemname from t) di

Upvotes: 2

Related Questions