MYSQL to sum income by dates and payment type-CodePudding

I want to get the income sum of each day between a period BUT separated by payment type.

My query so far is:

SELECT event_date, sum(amount) FROM moneyflow WHERE `event_date` BETWEEN "2022-10-01" AND "2022-10-03" 
GROUP BY event_date ORDER BY `event_date`

The results from it are:

but I would like them to be separated by payment type, How should I change the query to achieve the results in the way I want them? for example Thanks for your help

CodePudding user response：

One way to do it would be by using case statements:

SELECT event_date,
    sum(case when payment_type = "cash" then amount else 0 end) as cash,
    sum(case when payment_type = "card" then amount else 0 end) as card,
    sum(amount) total
FROM moneyflow
WHERE `event_date` BETWEEN "2022-10-01" AND "2022-10-03" 
GROUP BY event_date
ORDER BY `event_date`

Working SQL Fiddle