How to group and sum data based on dynamic where condition

svkks 0
Viewed 24

How to group and sum data based on dynamic where condition? I have to sum rows based on given dates. I have different dates that will passed as a parameter and have to sum item cost.

Example

enter image description here

Given above image from table without summing item_cost. Here I have to sum item_cost field based on bill_date field. Dates can vary which will be taken from another table and have to sum.

Below is query I have to used to get all item cost with summing

SELECT item_cost, 
       bill_date 
FROM billing 
WHERE (bill_date <= "2022-03-30 13:00:00" || bill_date <= "2022-03-30 18:00:00")

Here I have to group and sum item_cost based on given date. Sum of item_cost of bill_date <= "2022-03-30 13:00:00" and in another row bill_date <= "2022-03-30 18:00:00"

mysql database sql
1 Answers

This will give both totals:

SELECT 
  second.d, 
  sum(CASE WHEN bill_date<=second.d THEN item_cost END) as SUM
FROM billing
CROSS JOIN second
GROUP BY second.d
ORDER BY second.d

output:

d SUM
2022-03-30 13:00:00 102333.69
2022-03-30 18:00:00 218490.36

see: DBFIDDLE

Luuk0
Related