sql - Find SUM of child table column is less than parent child total column

Question

I am trying since 4 hours to write a SQL query which can show records if child table transections(amount) exceed the maximum limit defined in parent table. TB1 is parent table while TB2 is child table. Here is the query:

SELECT 
    SUM(b.amount) AS sum_transection,
    a.amount AS total,
    sum_transection - total AS diff
FROM 
    dbo.tb1 a INNER JOIN  dbo.tb2 b ON
    a.grp = b.grp AND a.head = b.head 
WHERE
     sum_transection - total >0

question from:https://stackoverflow.com/questions/65848018/find-sum-of-child-table-column-is-less-than-parent-child-total-column

Answer 1

I think you should use Group by for your requirement, and can't use Alias in Where condition in your case. (I'm using SQL Server, not sure if you have different tools) I made some changes like that

SELECT 
    SUM(b.amount) AS sum_transection,
    a.amount AS total,
    SUM(b.amount) - a.amount AS diff
FROM 
    dbo.tb1 a INNER JOIN  dbo.tb2 b ON
    a.grp = b.grp AND a.head = b.head 
GROUP BY a.grp, a.amount
having SUM(b.amount) - a.amount >0