0

I'm trying to create a query for this problem.

Suppose I have these two tables:

Acceptor table:

ID  TYPE        QTY
--  ----------  ------------------
1   AB+          1
2   O-           5
3   O+           1
4   O-           5
5   O-           5
6   AB-          1
7   AB+          1

Donor table:

ID  TYPE        QTY
--  ----------  ------------------
1   AB+          2
2   O-           5
3   O+           3
4   O-           5
5   O-           3
6   AB-          1
7   AB+          3

I need a query to select the types of blood, and the difference between the quantities in cases where the SUM of Acceptor.QTY is greater than the SUM of Donor.QTY.

So in this case, Donor's O- => 5 + 3 + 5 = 13 and the Acceptor's O- => 5 + 5 + 5 = 15.

The query would have to return

O-   2
2

You can use a group by on each table to get the result you're looking for, then wrap the whole thing in a view to filter it. See this fiddle: https://www.db-fiddle.com/f/d313vqnsTkiXDXfiEPTwLt/4

Select shortages.Type, shortages.Shortage from (
    Select a.Type, a.QTY-d.QTY Shortage
    from 
        (Select Type, sum(QTY) QTY from Acceptors group by Type) a
         inner join 
        (Select Type, sum(QTY) QTY from Donors group by Type) d on a.type = d.type

) shortages
where shortages.shortage > 0

If you need to cover the situation where a value doesn't exist in the donors table at all you should replace the inner join with a left join and use a case statement or an ISNULL to replace the null values with zeros.

  • External framing is excess. Select a.Type, a.QTY-d.QTY Shortage ... on a.type = d.type HAVING shortage > 0 is enough. – Akina Jan 24 at 5:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.