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

SELECT *, IF(`pay_method`=1,SUM(`cash_paid`),0) AS Cash_Paid FROM orders o
GROUP BY DATE(`date`)

Can you please try this. this will give you total cash paid... for partailly paid cash you can create another query and add them both....

Answer 2

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`)

Answer 3

You need to use GROUP BY DAY(date)

Answer 4

In one query you could try like this:

select DATE(date), sum(case when pay_method = 1 then purchase_price else cash_paid end) from orders group by DATE(date)

btw. not sure if in mysql you can go with "case" inside sum function.