Finding rows where totals do not match

All we need is an easy explanation of the problem, so here it is.

MariaDB MySQL

Trying to do what seems like a basic query (join) on two tables.

Receipts

id date supplier total

Parts

id receipt_id partType subtotal

Trying to get the row from the first table when the totals of subtotal of each parts.receipt_id does not equal receipts.id.total.

My butchered attempts include:

SELECT `receipts`.*
FROM `receipts` 
LEFT JOIN ON `receipts`.`id`=`parts`.`receipt_id` 
WHERE SUM(`parts`.`subtotal`) != `receipts`.`total` 
GROUP BY `parts`.`receipt_id`;

Current work around using compare in PHP (very inefficient).

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can use subqueries as long they only return 1 Value (row)

SELECT 
    r.*
FROM
    receipts r
WHERE
    (SELECT 
            SUM(parts.subtotal)
        FROM
            parts
        WHERE
            receipt_id = r.id) != r.total;

This works as you can see.

If your data have another design, pleae provide a changed dbfille

CREATE TABLE receipts (id  int,   `date`date,     supplier varchar(10),   total DECIMAL(10,2))
INSERT INTO receipts VALUES (1,NOW(),'a',100.1),(2,NOW(),'b',200.2)
CREATE TABLE parts (id    int, receipt_id     int , partType varchar(10),     subtotal DECIMAL(10,2))
INSERT INTO parts VALUEs (1,1,'a',20.0),(2,1,'a',20.0),(3,1,'a',20.1),(4,1,'a',20.0),(2,1,'a',20.0),
(6,2,'a',50.0),(7,2,'a',150.0)
SELECT 
    r.*
FROM
    receipts r
WHERE
    (SELECT 
            SUM(parts.subtotal)
        FROM
            parts
        WHERE
            receipt_id = r.id) != r.total;
id | date       | supplier |  total
-: | :--------- | :------- | -----:
 2 | 2021-06-17 | b        | 200.20
 SELECT 
    r.* ,  (SELECT 
            SUM(parts.subtotal)
        FROM
            parts
        WHERE
            receipt_id = r.id) sumsubtotal
 FROM
    receipts r
            
id | date       | supplier |  total | sumsubtotal
-: | :--------- | :------- | -----: | ----------:
 1 | 2021-06-17 | a        | 100.10 |      100.10
 2 | 2021-06-17 | b        | 200.20 |      200.00

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply