php - Get paid cash amount grouped by day

Question

I have a table called orders this table contains all the information we need.

I'll just list the columns we need for the query:

• purchase_price (always contains the total amount paid for the order)
• pay_method ((int) when 1 it means the full purchase_price was paid for in cash, when 3 it means purchase_price was only paid partially in cash, other values mean other paying means so ignore them.)
• cash_paid (when the pay_method is 3 this column contains the amount of cash paid for the order, note that this is not the total price, it's just the part of purchase_price that was paid for in cash. When pay_method is NOT 3 this field's value is 0
• date ((datetime)simply the date+time on which the order was placed)

The idea is pretty simple. we need to get the total amount of cash payed grouped by day. But we found this a pretty hard task.

PS: I'm playing around with this problem in PHP using MySQL, so it's alright to use multiple queries and/or use some PHP script.

Answer 1

Use MySQL IF-function:

If the pay_method is 1, take the purchase_price field. Else take the cash_paid field.

SELECT DATE(`date`), SUM(IF(pay_method=1,purchase_price,cash_paid)) 
FROM yourtable
/*WHERE pay_method IN (1,3) -- if you only want those */
GROUP BY DATE(`date`)