CODEWITHSUNDEEP
mysql
Europeuser
Europeuser

Reputation: 952

MYSQL to sum income by dates and payment type

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

enter image description here

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:

enter image description here

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 enter image description here Thanks for your help

Upvotes: 0

Views: 27

Answers (1)

diiN__________
diiN__________

Reputation: 7656

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

Upvotes: 1

Related Questions