asked on

INVALID SUM RESULTS from SQL JOIN

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

Jim Dettman (EE MVE)

The simple way to do this is to nest two queries:

1. Write one query to return the sum on the detail table.
2. In a second query, include the main table and the query you wrote in #1 and join them on the code field.
3. You'll now have both the main table total and the sum'd total of the detail records available.

Jim.

pclarke7

ASKER

Hi Jim,
I get the 1st Part which does give me the correct detail quantity but how do I include this in an outer query ?

SELECT b.code AS "Code" , SUM(b.quantity) AS "Detail Total"
FROM Detail b
GROUP BY b.code

Code Detail Total
AAA 60

Ryan Chong

try this:

Select a.Code,
SUM(b.quantity) as "Header Total",
SUM(b.quantity) as "Detail Total"
FROM Header
INNER JOIN Detail
ON Header.Code = Detail.Code
Group By a.Code

Open in new window

pclarke7

ASKER

Hi Ryan,

unfortunately that gives me the same incorrect result of 420 for AAA. I have managed to get it working with the following code

Select a.code as "Code",SUM(a.quantity) as "Header Total","Detail Total"= (Select sum(b.quantity) FROM Detail b where b.code=a.code)
FROM Header a
GROUP BY a.code

Code Header Total Detail Total
AAA 60 60

HOWEVER whilst it work great in SQL it does not work in Oracle. When I run in Oracle it gives me an error "FROM keyword not found where expected". Any idea how to get this working in Oracle db ?

Ryan Chong

ooops, for my example above, it should be as:

Select Header.Code,
SUM(Header.quantity) as "Header Total",
SUM(Detail.quantity) as "Detail Total"
FROM Header
INNER JOIN Detail
ON Header.Code = Detail.Code
Group By Header.Code

Open in new window

Scott Pletcher

SELECT H.Code, SUM(H.Quantity) AS [Header Total], MAX([Detail Total]) AS [Detail Total]
FROM Header H
INNER JOIN (
SELECT Code, SUM(Quantity) AS [Detail Total]
FROM Detail
GROUP BY Code
) AS D ON D.Code = H.Code
GROUP BY H.Code
ORDER BY H.Code

Ryan Chong

what about this?

select *
from
(
    Select 'Header' Tag, Code , SUM(quantity) Total FROM Header Group By Code union all
    Select 'Detail' Tag, Code , SUM(quantity) Total FROM Detail Group By Code
) a

Open in new window

pclarke7

ASKER

Same result AAA=420. Inner join will not work where there is a many to many relationship.

slightwv (䄆 Netminder)

This was tested in Oracle and SQL Server:

with header_totals as (
	select code, sum(quantity) header_total from header group by code
),
detail_totals as (
	select code, sum(quantity) detail_total from detail group by code
)
select h.code, header_total, detail_total
from header_totals h
	join detail_totals d on h.code=d.code
;

Open in new window

SQL Server fiddler here:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b7dc2bce3d53a536870506e5b10143bd

pclarke7

ASKER

Thanks Scott,
this works also in SQL but does not work for Oracle. I know I failed to mention that I need it for oracle , but that's the requirement

Scott Pletcher

I didn't test the code from my earlier query, https://www.experts-exchange.com/questions/29180633/INVALID-SUM-RESULTS-from-SQL-JOIN.html#a43077172 , repeated here, but this is the standard way to do this type of join in a relation db:

SELECT H.Code, SUM(H.Quantity) AS [Header Total], MAX([Detail Total]) AS [Detail Total]
FROM Header H
INNER JOIN (
SELECT Code, SUM(Quantity) AS [Detail Total]
FROM Detail
GROUP BY Code
) AS D ON D.Code = H.Code
GROUP BY H.Code
ORDER BY H.Code

Scott Pletcher

Hmm, really, doesn't work in Oracle? Odd, it's truly standard SQL. Let me check on whether it's a syntax error of some kind or not.

Scott Pletcher

Ahh, the brackets around the names. I forget how Oracle handles names with special chars (spaces in this case), maybe dbl quotes?

slightwv (䄆 Netminder)

Oracle doens't like the '[]' and "as" for the alias.

This works in Oracle:

SELECT H.Code, SUM(H.Quantity) AS Header_Total, MAX(Detail_Total) AS Detail_Total
FROM Header H
INNER JOIN (
    SELECT Code, SUM(Quantity) AS Detail_Total
    FROM Detail
    GROUP BY Code
) D ON D.Code = H.Code
GROUP BY H.Code
ORDER BY H.Code
/

Open in new window

Scott Pletcher

SELECT H.Code, SUM(H.Quantity) AS "Header Total", MAX("Detail Total") AS "Detail Total"
FROM Header H
INNER JOIN (
SELECT Code, SUM(Quantity) AS "Detail Total"
FROM Detail
GROUP BY Code
) AS D ON D.Code = H.Code
GROUP BY H.Code
ORDER BY H.Code

slightwv (䄆 Netminder)

>>maybe dbl quotes?

Double quotes works as well but it is seen as a bad habit to get into...

SELECT H.Code, SUM(H.Quantity) AS "Header Total", MAX("Detail Total") AS "Detail Total"
FROM Header H
INNER JOIN (
    SELECT Code, SUM(Quantity) AS "Detail Total"
    FROM Detail
    GROUP BY Code
) D ON D.Code = H.Code
GROUP BY H.Code
ORDER BY H.Code
/

Open in new window

slightwv (䄆 Netminder)

@Scott:

Sure we cross posted. Oracle doesn't like "AS" on the alias:

) AS D ON D.Code = H.Code
  *
ERROR at line 7:
ORA-00905: missing keyword

Open in new window

Scott Pletcher

Interesting. It's optional in SQL Server, so it can be removed.

[I haven't worked full-time professionally on Oracle since Oracle 8.]

pclarke7

ASKER

Hi Scott & SlightWV,
Thanks for your help. This is working in Oracle - thanks. One last question

How can I only select records whose Header & Detail quantities do not match. I would like to condition where "Header Total" <>"Detail Total" but whilst it recognizes "Detail Total" it does not recognise "Header Total"

SELECT H.Code, SUM(H.Quantity) AS "Header Total", MAX("Detail Total") AS "Detail Total"
FROM Header H
INNER JOIN (
SELECT Code, SUM(Quantity) AS "Detail Total"
FROM Detail
GROUP BY Code
) D ON D.Code = H.Code AND "Header Total" <> "Detail Total"
GROUP BY H.Code
ORDER BY H.Code

slightwv (䄆 Netminder)

You cannot use an alias in the same level it is created. You are also limited in where you han use aggregate functions in the SQL.

I would probably go with the CTE example:

with header_totals as (
	select code, sum(quantity) header_total from header group by code
),
detail_totals as (
	select code, sum(quantity) detail_total from detail group by code
)
select h.code, header_total, detail_total
from header_totals h
	join detail_totals d on h.code=d.code
where header_total <> detail_total
;

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

pclarke7

ASKER

THanks for the great response. Got it sorted and learned a few things. Much appreciated