Jump to content

MySQL SELECT to get total sales by payment method and status


thara

Recommended Posts

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.

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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 by thara
Link to comment
Share on other sites

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)

 

 

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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;

 

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.