I would like to verify if the SQL query I wrote is correct or can be improved based on the problem statement given below.
Table t1 stores the payment done by customers and Table t2 contains number of items purchased by customer and the status of order. I would like to write a query to verify if for each order, payment = qty*price_per_item. If this condition is false, print the difference in a new column.
===== Table t1 ===== customer_id payment($) 1 6 2 20 3 10 ===== Table td ===== customer_id qty price_per_item($) order_status 1 3 2 complete 1 2 4 cancelled 2 2 3 complete 2 3 5 complete 3 5 2 complete 3 4 7 cancelled
This is what I have written as SQL query, but I am not sure if it’s correct.
select A.customer_id, B.Rev, A.Multiply, (B.Rev - A.Multiply) as diff, case when (B.Rev - A.Multiply) > 0 then 1 else 0 end as Boolean from (select td.order_id, td.customer_id, sum(td.qty * td.price_per_item) over(partition by td.customer_id) As Multiply from td where td.order_status in 'complete') A inner join (select t1.customer_id, sum(t1.payment) Rev from t1 group by t1.customer_id) B on A.customer_id = B.customer_id;
You want to ensure that, for each customer/order tuple, the order price was fully paid. Your sample data is missing the
order_id, but I will assume that it is there, since you seem to mean it.
In essence, that’s a full outer join. In MySQL, we can emulate that with
union all. The order table has several records per order, so we need to aggregate it before unioning (while ignoring cancelled orders). Then, the outer query groups records having the same customer and order_id, and compares the order price to the paid price in a
select customer_id, order_id, max(price) total_price, max(paid) total_paid from ( -- get the amount ordered per customer_id/order_id select customer_id, order_id, sum(qty * price_per_item) price, null paid from td where order_status != 'cancelled' group by customer_id, order union all -- get the amount paid per customer/order_id select customer_id, order_id, null, payment from t1 ) t group by customer_id, order_id having not max(price) = max(paid)
select order_status, price_per_item, qty, payment, price_per_item*qty as Multiply, payment- price_per_item*qty , case when payment- price_per_item*qty = 0 then 0 else 1 end as Boolean from td a inner join t1 on t1.customer_id = a.customer_id
Let us know
By the way you dont have an order_id in your tables