SELECT a date FROM table1 then COUNT how many results WHERE payment='cash' AND date=table1.date AS cash and again for 'credit'

Question

This is hard :) I try to SELECT date, GROUP BY date and count every column WHERE payment = 'cash' and 'card'

My table is something like this:

id | amount | payment_method | create_date
----------
1  | 10.00  | 'cash'       | 2020-09-10
----------
2  | 15.00  | 'card'       | 2020-09-10
----------
3  | 23.00  | 'cash'       | 2020-09-11

I expect this type of results:

date       | cash | card |
----------
2020-09-10 | 1    | 1    |
----------
2020-09-11 | 1    | 0    |

This one it's working for counting all payments:

SELECT DATE(op.create_date) AS cdate, COUNT(op.id) AS total 
FROM order_payment op 
GROUP BY cdate

And I try to work with this:

SELECT
DATE(op.create_date) AS cdate,
COUNT(
 SELECT *
 FROM order_payment
 WHERE DATE(create_date) = rdate AND payment_method = 'cash'
 ) AS cash,
COUNT(
 SELECT *
 FROM order_payment
 WHERE DATE(create_date) = rdate AND payment_method = 'card'
 ) AS card
FROM order_payment op
GROUP BY cdate

but it give me this error:

Query 1 ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM order_payment o WHERE DATE(o.create_date) = rdate AND payment_me' at line 4

Someone can help me with this?

score 1 · Answer 1 · answered Sep 25 '20 at 21:32

Use conditional aggregation:

select create_date,
    sum(payment_method = 'cash') cash,
    sum(payment_method = 'card') card
from mytable
group by create_date

If create_date has a time component, you can use date(create_date) instead of create_date in the query.

score 1 · Answer 2 · answered Sep 25 '20 at 21:33

1

SELECT 
    create_date,
    SUM(IF(payment_method = 'cash', 1, 0)) AS "Cash",
    SUM(IF(payment_method = 'card', 1, 0)) AS "Card"
FROM order_payment 
GROUP BY create_date

answered Sep 25 '20 at 21:33

Dark Knight

6,116
1
15
37

Ok , booth of them work like a sharm, the difference with @GMB answer is that you make an if statement, can I know why? – AlexNica Sep 25 '20 at 21:46
GMB used the shorthand syntax, both are same. How it works is if `a=b` then it returns true which will considered as 1 and false will considered as 0, so would return same sum for both of them. – Dark Knight Sep 25 '20 at 22:23

SELECT a date FROM table1 then COUNT how many results WHERE payment='cash' AND date=table1.date AS cash and again for 'credit'

2 Answers2