CODEWITHSUNDEEP
sqlpostgresql
Liam Nichol
Liam Nichol

Reputation: 67

Compare the sum of rows with shared reference in one table with a single value in another

I have a list of my stock in one table, stock, and I reserve portions of that stock to an order in another table, orders

I have a feeling my data is no longer in a good state, and I need to compare the amount I have reserved from a chunk of stock in the orders table to the total I have in the reserved stock table.

Each stockref has a unique entry in stock but can occur multiple times in orders if it represents a quantity that is split across multiple orders.

Stock Table

Order Table

The ideal query would show me stockrefs that are over-reserved. Once I have these I can then check my code to see what caused the issue and fix it before I go bust selling items I no longer have stock of.

Upvotes: 0

Views: 28

Answers (1)

Deepak Kumar
Deepak Kumar

Reputation: 308

I think this should resolve your problem

    Select s.reservedqty,s.stockref,O.Total_reservedqty from stock S join 
    (
    Select sum(reservedqty) as Total_reservedqty,stockref from orders  group by stockref
    ) O
    On S.stockref=O.stockref
    where O.Total_reservedqty !=s.reservedqty

Upvotes: 1

Related Questions