0

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

Europeuser
  • 934
  • 1
  • 9
  • 32
  • I think you're looking for a pivot table, like described [here](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql). – Sam020 Oct 25 '22 at 14:00

1 Answers1

1

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

diiN__________
  • 7,393
  • 6
  • 42
  • 69
  • diiN :) TRUE :))), maybe is time to rest , Thanks, I will accept it in minute – Europeuser Oct 25 '22 at 13:45
  • Well but after I tested I discovered it prints 3 rows per date instade of 1 row with 2 amounts.. Isn't it possible to achieve the results per date in 1 row? – Europeuser Oct 25 '22 at 13:49
  • Exactly, I just was going to answer with the same query when I saw your answer! Accepted, Thank you ! – Europeuser Oct 25 '22 at 14:09