Hello,
I have two simple tables Header & Detail
Header
Code Quantity
AAA 10
AAA 20
AAA 30
Detail
Code Quantity
AAA 5
AAA 5
AAA 10
AAA 10
AAA 10
AAA 15
AAA 5
I need a SQL statement which will validate that the Total for Code AAA on the header table matches the Total for Code AAA on the detail table
SELECT a.code AS "Code", SUM(a.quantity ) AS "Header Total", SUM(b.quantity) AS "Detail Total"
FROM Header a, Detail b WHERE a.code=b.code
GROUP BY a.code
However I am getting the following for AAA
Code Header Total Detail Total
AAA 420 180
I understand that because it is not a 1 for 1 relationship that this is causing the sum to be aggregated. How can I amend the query to give me the result I require
Code Header Total Detail Total
AAA 60 60
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.