SQL verify columns by joining 2 tables

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;

2 Answers

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 having clause.

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)

Try this:

     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

Archive from: https://stackoverflow.com/questions/59018530/sql-verify-columns-by-joining-2-tables

Leave a Reply

Your email address will not be published. Required fields are marked *