MySQL SELECT to get total sales by payment method and status

[thara]

I have some sales data as below:

SELECT bv.sale_time
     , amount_due
     , round(sum(amount_paid), 2) as paid
     , m.payment_method_id
     , m.method
     , bt.payment_status
FROM basket_amount_due bv
  JOIN basket bt USING(basket_id)
  LEFT JOIN basket_payment p USING (basket_id)
  JOIN payment_method m USING(payment_method_id)
GROUP BY bv.basket_id;

+---------------------+------------+---------+-------------------+-------------------+----------------+
| sale_time           | amount_due | paid    | payment_method_id | method            | payment_status |
+---------------------+------------+---------+-------------------+-------------------+----------------+
| 2021-09-18 12:19:04 |    1170.00 | 1170.00 |                 1 | CASH              | paid           |
| 2021-09-18 12:19:39 |     756.60 |    0.00 |                 1 | CASH              | due            |
| 2021-09-18 12:20:22 |    1115.50 | 1000.00 |                 1 | CASH              | partial        |
| 2021-09-18 12:21:47 |     990.00 |  990.00 |                 4 | Cash on Delivery  | paid           |
| 2021-09-18 12:23:33 |     698.40 |    0.00 |                 4 | Cash on Delivery  | due            |
| 2021-09-18 12:29:45 |    2070.00 | 2070.00 |                 2 | Credit/Debit Card | paid           |
+---------------------+------------+---------+-------------------+-------------------+----------------+
6 rows in set (0.004 sec)

My question is, Now I need to get total sales by payment method and payment status. That mean I want,

    total cash sales (= cash(paid) + Card (paid) + Cash on Delivery (paid))
    total credit sales (= cash(due) + cash(partial) + Cash on Delivery (due))
    total cash on delivery sales (= Cash on Delivery (paid) + Cash on Delivery (due))
    total card sales

This is the query I have so far..

SELECT DATE(bv.sale_time)
     , CASE p.payment_method_id
        WHEN (1 AND 2) THEN sum(amount_due) ELSE 0 END AS total_cash_sales
     , CASE p.payment_method_id
        WHEN 4 THEN sum(amount_due) ELSE 0 END AS total_credit_sales
FROM basket_amount_due bv
  JOIN basket bt USING(basket_id)
  LEFT JOIN basket_payment p USING (basket_id)
  JOIN payment_method m USING(payment_method_id)
WHERE DATE(bv.sale_time) >= CURDATE()
      AND DATE(bv.sale_time) < CURDATE() + INTERVAL 1 DAY
GROUP BY bv.sale_time;

Hope somebody may help me out to figure this out.

[Barand]

Firstly, can you show me the record where the "payment_method_id" is both 1 and 2 ?

You to need to change around the syntax of that first CASE statement

, CASE WHEN p.payment_method_id IN (1, 2) THEN sum(amount_due) ELSE 0 END AS total_cash_sales

 

1 hour ago, thara said:

WHERE DATE(bv.sale_time) >= CURDATE()
      AND DATE(bv.sale_time) < CURDATE() + INTERVAL 1 DAY

 = where date on or after today but before tomorrow.

Isn't that just WHERE DATE(bv.sale_time) = CURDATE()

Lastly, are you sure you want to GROUP BY sale_time? That gives you a total for each second, which is probably individual sales - not much of a summary.

Edited September 18, 2021 by Barand

[thara]

Thank you Sir.

13 minutes ago, Barand said:

Firstly, can you show me the record where the "payment_method_id" is both 1 and 2 ?

 

select * from payment_method;
+-------------------+-------------------+
| payment_method_id | method            |
+-------------------+-------------------+
|                 1 | CASH              |
|                 2 | Credit/Debit Card |
|                 3 | Store Credit      |
|                 4 | Cash on Delivery  |
|                 5 | Gift card         |
|                 6 | Credit            |
+-------------------+-------------------+
6 rows in set (0.001 sec)

select * from basket_payment;
+-------------------+-------------------+-----------+-------------+----------------+---------------------+--------------+
| basket_payment_id | payment_method_id | basket_id | amount_paid | amount_balance | date_paid           | payment_note |
+-------------------+-------------------+-----------+-------------+----------------+---------------------+--------------+
|                 1 |                 1 |         1 |     1170.00 |           0.00 | 2021-09-18 12:19:04 |              |
|                 2 |                 1 |         2 |        0.00 |           0.00 | 2021-09-18 12:19:39 |              |
|                 3 |                 1 |         4 |     1000.00 |           0.00 | 2021-09-18 12:20:22 |              |
|                 4 |                 4 |         5 |      990.00 |           0.00 | 2021-09-18 12:21:47 |              |
|                 5 |                 4 |         7 |        0.00 |           0.00 | 2021-09-18 12:23:33 |              |
|                 8 |                 2 |        10 |     2070.00 |           0.00 | 2021-09-18 12:29:45 |              |
+-------------------+-------------------+-----------+-------------+----------------+---------------------+--------------+
6 rows in set (0.001 sec)

 

Edited September 18, 2021 by thara

[Barand]

None of those have a value of both 1 AND 2. That would mean, like Schrodinger's cat, they have two values at the same time,

Some, though, do have a value of 1 OR 2

[thara]

Ooops! Yes sir It should be OR not AND...

This is my updated query and its result as below. Also I changed GROUP BY to payment_method_id. 
 

SELECT DATE(bv.sale_time)
     , CASE
        WHEN p.payment_method_id IN (1, 2)
          THEN sum(amount_due)
          ELSE 0
        END AS total_cash_sales
     , CASE p.payment_method_id
        WHEN 4 THEN sum(amount_due) ELSE 0 END AS total_credit_sales
FROM basket_amount_due bv
  JOIN basket bt USING(basket_id)
  LEFT JOIN basket_payment p USING (basket_id)
  JOIN payment_method m USING(payment_method_id)
WHERE DATE(bv.sale_time) = CURDATE()
GROUP BY p.payment_method_id;

+--------------------+------------------+--------------------+
| DATE(bv.sale_time) | total_cash_sales | total_credit_sales |
+--------------------+------------------+--------------------+
| 2021-09-18         |          3042.10 |               0.00 |
| 2021-09-18         |          2070.00 |               0.00 |
| 2021-09-18         |             0.00 |            1688.40 |
+--------------------+------------------+--------------------+
3 rows in set (0.011 sec)

 

 

 

[Barand]

If I were showing totals by "Payment method" I would find it helpful to show the method descriptions in the row output.

The main question - is that correct and what you wanted?

[thara]

Sir, I really want to get a sales report for the current date. Through this report, I want to get the following data.

• Total Cash Sales                          = (cash(paid) + card (paid) + cash on delivery (paid))
• Total Credit Sales                    = (cash(due) + cash(partial) + Cash on Delivery (due))
• Total Cash on Delivery Sales = (Cash on Delivery (paid) + Cash on Delivery (due))
• Total card sales

Sir, is it possible to get those data in a single query?

[Barand]

Is it correct that some combinations of method/status should be included in more than one total?

[thara]

Sir, I think that's how it should be. But the relevant calculations should be changed as follows.

Total Cash Sales                       = (cash(paid) + cash(partial paid) + cash on delivery (paid))

Total Credit Sales                     = (cash(due) + cash(partial due) + Cash on Delivery (due))

Total Cash on Delivery Sales  = (cash on Delivery (paid) + Cash on Delivery (due))

Total card sales                        = (card(paid)

[Barand]

Your payment status values are "Paid/Due/Partial"

Do we have a couple of new Schrodinger cats in your latest calculation requirements...

Edited September 18, 2021 by Barand

[thara]

Sorry for not giving a clear explanation sir.

I meant "partial paid" and "partial due", suppose the store made a sale worth 1000, where the customer pays 700 and keeps the remaining 300 as payable to the store.

Accordingly

partial paid = 700

partial due = 300

[Barand]

Here's my attempt

SELECT sale_date
     , sum(cashpaid) + sum(cashpartpaid) + sum(codpaid) as total_cash_sales
     , sum(cashdue) + sum(cashpartdue) + sum(carddue) + sum(coddue) as total_credit_sales
     , sum(codpaid) + sum(coddue) as total_cod_sales
     , sum(cardpaid) as total_card_sales
FROM (
		SELECT bad.basket_id
			 , bad.amount_due as due
			 , SUM(p.amount_paid) as paid
			 , p.payment_method_id
			 , b.payment_status
			 , DATE(bad.sale_time) as sale_date
			 , CASE WHEN payment_method_id = 1 AND payment_status = 'paid'
					THEN sum(p.amount_paid) ELSE 0
					END as cashpaid
			 , CASE WHEN payment_method_id = 1 AND payment_status = 'due'
					THEN bad.amount_due ELSE 0 
					END as cashdue
			 , CASE WHEN payment_method_id = 1 AND payment_status = 'partial'
					THEN SUM(p.amount_paid) ELSE 0
					END as cashpartpaid
			 , CASE WHEN payment_method_id = 1 AND payment_status = 'partial'
					THEN bad.amount_due - SUM(p.amount_paid) ELSE 0
					END as cashpartdue
			 , CASE WHEN payment_method_id = 2 
					THEN SUM(p.amount_paid) ELSE 0
					END as cardpaid
			 , CASE WHEN payment_method_id = 2 
					THEN bad.amount_due - SUM(p.amount_paid) ELSE 0
					END as carddue
			 , CASE WHEN payment_method_id = 4 AND payment_status <> 'due'
					THEN SUM(p.amount_paid) ELSE 0
					END as codpaid
			 , CASE WHEN payment_method_id = 4 AND payment_status = 'due' 
					THEN bad.amount_due ELSE 0
					END as coddue            
		FROM basket_amount_due bad
			 JOIN basket b USING (basket_id)
			 LEFT JOIN basket_payment p USING (basket_id)
		GROUP BY basket_id, payment_method_id 
     ) detail
GROUP BY sale_date;

 

[thara]

Wow.... Thats the query I needed..

Many many thanks sir.