CODEWITHSUNDEEP
mysql
Devesh Agrawal
Devesh Agrawal

Reputation: 9212

How to select customer's final balance for all customer in mysql

I have 3 tables. 

table_customers - customer_id, name
table_orders - order_id, customer_id, order_datetime
table_wallet - customer_id, amount, type  // type 1- credit, type 2- debit

I need to get all customers, their total balance, and their last order date and order id. This is my query.

SELECT 
C.customer_id, 
C.name, 
COALESCE( SUM(CASE WHEN type = 2 THEN -W.amount ELSE W.amount END), 0) AS value,
COALESCE( max( O.order_id  ) , '0' ) AS last_order_id, 
COALESCE( max( date( O.order_datetime ) ) , '0000-00-00' ) AS last_order_date
FROM 
table_customers as C 
LEFT JOIN 
table_wallet as W 
ON C.customer_id = W.customer_id 
LEFT JOIN
table_orders AS O
ON W.customer_id = O.customer_id
group by C.customer_id
ORDER BY C.customer_id

Everything is coming correct except customer's total value. From result it seems its getting added multiple times.

What is wrong in query? Can anyone help me on this?

Upvotes: 0

Views: 713

Answers (1)

tomb
tomb

Reputation: 400

This is doing a many-to-many join on table_customers to table_orders, which will mess with your sums. Rather do this:

SELECT C.customer_id
, C.name
, IFNULL((SELECT SUM(IF(W.type=2, -1*W.amount, W.amount))
    FROM table_wallet W 
    WHERE C.customer_id = W.customer_id),0) AS value
, IFNULL((SELECT MAX(DATE(O.order_id))
    FROM table_orders O 
    WHERE C.customer_id = O.customer_id),'0') AS last_order_id
, IFNULL((SELECT MAX(DATE(O.order_datetime))
    FROM table_orders O 
    WHERE C.customer_id = O.customer_id),'0000-00-00') AS last_order_date
FROM table_customers as C 
ORDER BY C.customer_id

This will return one row per customer, then subquery the fields you want. I've substituted IFNULL for COALESCE as I find it cleaner, but this is a preference thing.

Upvotes: 1

Related Questions