# Compare sum of columns between tables

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
``````

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