sql find parent table where sum of child column not equal to parent column

Question

hey guys i am trying to find the a bill from billMaster where the sum of billDetails.total is not equal to billMaster.remainingAmount

NB this is a one to many relationship where one bill can contain more billdetails

i tried the below query and got an sql error

/* SQL Error (1111): Invalid use of group function */


SELECT a.id AS billMAsterId FROM eBillMaster AS a JOIN eBillDetail AS b ON a.id = b.billId
WHERE SUM(b.total) !=  b.remainAmount GROUP BY a.id 


SELECT a.remainAmount, a.id AS BillId FROM eBillMaster a JOIN (SELECT MAX(id) AS id FROM eBillMaster) b JOIN eBillDetail c ON (a.id - c.billId) WHERE SUM(c.total) != a.remainAmount

both queries returned the same error i gess its on how i used the sum on where close. But the sad thing is that i cant sole the problem.. Any response will be appreciated.

now assume i want to get recent user bill that meets the above condition . Note the billMaster has a column called user_id. how will the new query look like.

The answer will depend on the DBMS you are using: Oracle? SQL Server? MySql? Also, the fact that you're asking this question indicates a flaw in the database design. Derived or calculated values (like remainAmount) should not be stored in the database at all. — Nicholas Hunter, May 10 '21 at 21:00
How would we know what constitutes as "recent"? Is it the higher user_id column? You should provide an example of the data and the expected result. — Bruno Canettieri, May 10 '21 at 22:03

Serg · Answer 1 · 2021-05-11T06:52:25.450

0

This is standard SQL, Aggregated values are filtered in the HAVING clause.

SELECT a.id AS billMAsterId 
FROM eBillMaster AS a 
JOIN eBillDetail AS b ON a.id = b.billId
GROUP BY a.id, a.remainAmount
HAVING SUM(b.total) != a.remainAmount

Some Sql engines allow to omitt a.remainAmount from GROUP BY when eBillMaster.id is PK. One more (exotic a bit) option

SELECT a.id AS billMAsterId 
FROM eBillMaster AS a 
JOIN eBillDetail AS b ON a.id = b.billId
GROUP BY a.id
HAVING SUM(b.total) != AVG(a.remainAmount)

edited May 11 '21 at 06:52

answered May 10 '21 at 20:36

Serg

22,285
5
21
48

This is wrong! Each line would join master and detail, so sum(a.remainAmount) is not what you want. – Bruno Canettieri May 10 '21 at 20:45
@BrunoCanettieri, provided `a.id` is a primary key, `SUM(a.remainAmount)` == `a.remainAmount` when `GROUP BY a.id`. – Serg May 10 '21 at 20:48
This is not wrong if `a.id` is *the* primary key (like it seems reasonable to assume). But in this case you can simply write `HAVING SUM(b.total) <> a.remainAmount`, because `remainAmount` functionally depends on the PK and is automatically covered by `GROUP BY a.id` - as per definition in standard SQL, and as implemented in PostgreSQL (at least). – Erwin Brandstetter May 10 '21 at 20:55
Hey, sorry. read my comment and it seemed a little aggressive. But I really do believe it is wrong. Take the master as (1, 10) and details with two lines (1, 10) and (1, 10), the join (id, remainamount, total) would appear as (1, 10, 10) and (1, 10, 10), so the group by and sum would return (1, 20, 20) - that in turn will make your query return that this id 1 is a valid response, which it does not seem to be the expected result. Check @Stu answer, it seems that was what you were aiming for. – Bruno Canettieri May 10 '21 at 21:12

score 0 · Answer 2 · answered May 10 '21 at 20:48

An example would make thinks much easier, but I think this is what you want:

SELECT m.id
FROM eBillMaster AS m 
JOIN (select billId, sum(total) sumTotal from eBillDetail group by billId) AS d ON m.id = d.billId
WHERE d.sumTotal != m.remainAmount;

Check it out here http://sqlfiddle.com/#!9/89dcfb/7

score 0 · Answer 3 · answered May 10 '21 at 20:49

It's more efficient tou use a cross apply also known as a lateral join - depending on your sql dialect, you haven't tagged your database

select a.id as billMAsterId 
from eBillMaster as a 
cross apply (
    select Sum(total) total
    from eBillDetail as b
    where b.billId = a.id 
)b
where a.remainAmount != b.total

To do the same with having clause you can do

select a.id as billMAsterId 
from eBillMaster as a 
join eBillDetail as b on a.id = b.billId
group by a.id, a.remainAmount
having Sum(b.total) != a.remainAmount

score 0 · Answer 4 · answered May 10 '21 at 20:57

You need to use the having clause and also group by remainamount

with ebillmaster (id) as(
    select 1 from dual union all
    select 2 from dual union all
    select 3 from dual),
ebilldetail (billid, total, remainamount) as(
    select 1, 4, 5 from dual union all
    select 2, 3, 4 from dual union all
    select 3, 3, 3 from dual union all
    select 4, 1, 2 from dual)
SELECT
    a.id AS billmasterid
FROM
    ebillmaster   a
    JOIN ebilldetail   b ON a.id = b.billid
GROUP BY
    a.id,
    b.remainamount
HAVING
    SUM(b.total) != b.remainamount

score 0 · Accepted Answer · answered May 11 '21 at 07:20

Thank you every one for your feedback it led me to the correct path and the modified query is

    SELECT m.propertyId, d.sumTotal, m.remainAmount, m.id
FROM eBillMaster AS m JOIN ( SELECT MAX(id) AS id FROM eBillMaster GROUP BY propertyId) b USING (id)
JOIN (select billId, sum(total) sumTotal from eBillDetail group by billId) AS d ON m.id = d.billId
WHERE d.sumTotal != m.remainAmount;

sql find parent table where sum of child column not equal to parent column

5 Answers5