CODEWITHSUNDEEP
mysqlsqlaggregate-functionsmysql-8.0
tedcurrent
tedcurrent

Reputation: 431

Get sum of values for multiple categories for a given list of dates

I need to write a query to get the sum of values for each category for a list of given dates. If a value doesn't exist for a category, we should get the value from the previous date. Basically something like "max per category per date". The end goal is a trend chart. If a previous value for a category doesn't exist, setting the value to 0 is fine.

See tables and result below:

Category

id name
1 savings
2 cash
3 stocks

Item

id categoryId value createdAt
1 1 100 2022-01-01
2 2 20 2022-01-01
3 3 500 2022-01-01
4 2 0 2022-01-02
5 3 1000 2022-01-03

Result

createdAt total
2022-01-01 620
2022-02-02 600
2022-02-03 1100

To get a result for a single date I could do something like this:

SELECT SUM(value) as total
FROM Category
LEFT JOIN (
    SELECT id, categoryId, value
    FROM Item
    WHERE id IN (
        SELECT MAX(id) FROM Item WHERE createdAt <= '2022-01-10' GROUP BY categoryId)
) items ON Category.id = items.categoryId;

I have absolutely no clue on how to approach doing this for multiple dates, eg. if my input would be every day in the month of January 2022. I'm running on MySQL 8.0.23. Also, if this is not feasible with a single query, I'm up for ideas. Do you have any suggestions?

Upvotes: 1

Views: 1419

Answers (2)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65363

One option uses window functions such as SUM() OVER () and LAG() such as

WITH i AS
(
 SELECT SUM(`value`) OVER (PARTITION BY `createdAt`,`categoryId` ORDER BY `createdAt`) AS total_sofar,
        LAG(`value`,1,0) OVER (PARTITION BY `categoryId` ORDER BY `createdAt`) AS lg,
        `createdAt`
   FROM Item 
)
SELECT DISTINCT `createdAt`,
       SUM(total_sofar) OVER (ORDER BY `createdAt`)-SUM(lg) OVER (ORDER BY `createdAt`) AS total
  FROM i
 ORDER BY `createdAt`

as you have MySQL DBMS of version 8.0. The trick is grouping(partitioning by categoryId along with the LAG at the first query)

Demo

Upvotes: 0

Zakaria
Zakaria

Reputation: 4796

Try this:

with u as
(select id as categoryId from Category),
v as
(select distinct createdAt from Item),
w as
(select * from u cross join v),
x as
(select createdAt, 
categoryId, 
(select value 
from Item 
where categoryId = w.categoryId and createdAt <= w.createdAt 
order by createdAt desc 
limit 1) as value
from w)
select createdAt, sum(value) as total
from x
group by createdAt

Basically getting all the combinations of the creation dates with the categoryIds, then using a subquery to get the value of the closest or equal date for each categoryId.

A Fiddle.

Upvotes: 1

Related Questions