<div>
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. &nbsp;&nbsp;
</div>

<div>
Hi Jim, 
I get the 1st Part &nbsp;which does give me the correct detail quantity but how do I include this in an outer query ? 
 
&nbsp;SELECT &nbsp;b.code AS &quot;Code&quot; , SUM(b.quantity) AS &quot;Detail Total&quot; 
&nbsp; FROM Detail &nbsp;b 
&nbsp; GROUP BY b.code 
 
Code &nbsp; &nbsp; &nbsp; &nbsp;Detail Total 
AAA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60
</div>

<div>
try this: 
 

<pre><code class="code-1 nolinks" id="code-20-43077127-1">Select a.Code,
SUM(b.quantity) as &quot;Header Total&quot;,
SUM(b.quantity) as &quot;Detail Total&quot;
FROM Header
INNER JOIN Detail
ON Header.Code = Detail.Code
Group By a.Code
</code></pre>
</div>

<div>
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 &quot;Code&quot;,SUM(a.quantity) as &quot;Header Total&quot;,&quot;Detail Total&quot;= (Select sum(b.quantity) FROM Detail b &nbsp;where b.code=a.code) 
&nbsp; FROM Header a 
&nbsp; GROUP BY a.code 
 
Code &nbsp; &nbsp; Header Total &nbsp; &nbsp; &nbsp; Detail Total 
AAA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 60 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 60 
 
HOWEVER whilst it work great in SQL it does not work in Oracle. When I run in Oracle it gives me an error &quot;FROM keyword not found where expected&quot;. Any idea how to get this working in Oracle db ?
</div>

<div>
ooops, for my example above, it should be as: 
 

<pre><code class="code-1 nolinks" id="code-20-43077169-1">Select Header.Code,
SUM(Header.quantity) as &quot;Header Total&quot;,
SUM(Detail.quantity) as &quot;Detail Total&quot;
FROM Header
INNER JOIN Detail
ON Header.Code = Detail.Code
Group By Header.Code
</code></pre>
</div>

<div>
 SELECT H.Code, SUM(H.Quantity) AS [Header Total], MAX([Detail Total]) AS [Detail Total] FROM Header H INNER JOIN ( &nbsp; &nbsp; SELECT Code, SUM(Quantity) AS [Detail Total] &nbsp; &nbsp; FROM Detail &nbsp; &nbsp; GROUP BY Code ) AS D ON D.Code = H.Code GROUP BY H.Code ORDER BY H.Code 
</div>

<div>
what about this? 
 

<pre><code class="code-1 nolinks" id="code-20-43077173-1">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
</code></pre>
</div>

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

<div>
This was tested in Oracle and SQL Server: 

<pre><code class="code-10 nolinks" id="code-20-43077184-1">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
;
</code></pre>
 
SQL Server fiddler here: 
<a href="https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b7dc2bce3d53a536870506e5b10143bd" rel="ugc">https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b7dc2bce3d53a536870506e5b10143bd</a>
</div>

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

<div>
I didn't test the code from my earlier query,&nbsp;<a href="https://www.experts-exchange.com/questions/29180633/INVALID-SUM-RESULTS-from-SQL-JOIN.html?anchorAnswerId=43077172#a43077172" rel="ugc">https://www.experts-exchange.com/questions/29180633/INVALID-SUM-RESULTS-from-SQL-JOIN.html#a43077172</a> , 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 ( &nbsp; &nbsp; SELECT Code, SUM(Quantity) AS [Detail Total] &nbsp; &nbsp; FROM Detail &nbsp; &nbsp; GROUP BY Code ) AS D ON D.Code = H.Code GROUP BY H.Code ORDER BY H.Code 
</div>

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

<div>
Ahh, the brackets around the names. &nbsp;I forget how Oracle handles names with special chars (spaces in this case), maybe dbl quotes?
</div>

<div>
Oracle doens't like the '[]' and &quot;as&quot; for the alias. 
 
This works in Oracle: 

<pre><code class="code-10 nolinks" id="code-20-43077192-1">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
/
</code></pre>
</div>

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

<div>
&gt;&gt;maybe dbl quotes? 
 
Double quotes works as well but it is seen as a bad habit to get into... 
 

<pre><code class="code-10 nolinks" id="code-20-43077194-1">SELECT H.Code, SUM(H.Quantity) AS &quot;Header Total&quot;, MAX(&quot;Detail Total&quot;) AS &quot;Detail Total&quot;
FROM Header H
INNER JOIN (
 SELECT Code, SUM(Quantity) AS &quot;Detail Total&quot;
 FROM Detail
 GROUP BY Code
) D ON D.Code = H.Code
GROUP BY H.Code
ORDER BY H.Code
/
</code></pre>
</div>

<div>
@Scott: 
 
Sure we cross posted. &nbsp;Oracle doesn't like &quot;AS&quot; on the alias: 

<pre><code class="code-1 nolinks" id="code-20-43077196-1">) AS D ON D.Code = H.Code
 *
ERROR at line 7:
ORA-00905: missing keyword
</code></pre>
</div>

<div>
Interesting. &nbsp;It's optional in SQL Server, so it can be removed. [I haven't worked full-time professionally on Oracle since Oracle 8.]
</div>

<div>
Hi Scott &amp;&nbsp;SlightWV, 
Thanks for your help. This is working in Oracle - thanks. One last question 
 
How can I only select records whose Header &amp;&nbsp;Detail quantities do not match. I would like to condition where &quot;Header Total&quot; &lt;&gt;&quot;Detail Total&quot; but whilst it recognizes &nbsp;&quot;Detail Total&quot; it does not recognise &quot;Header Total&quot; 
 
SELECT H.Code, SUM(H.Quantity) AS &quot;Header Total&quot;, MAX(&quot;Detail Total&quot;) AS &quot;Detail Total&quot; 
FROM Header H 
INNER JOIN ( 
&nbsp; &nbsp; SELECT Code, SUM(Quantity) AS &quot;Detail Total&quot; 
&nbsp; &nbsp; FROM Detail 
&nbsp; &nbsp; GROUP BY Code 
) D ON D.Code = H.Code AND &quot;Header Total&quot; &lt;&gt;&nbsp;&quot;Detail Total&quot; 
GROUP BY H.Code 
ORDER BY H.Code
</div>

<div>
You cannot use an alias in the same level it is created. &nbsp;You are also limited in where you han use aggregate functions in the SQL. 
 
I would probably go with the CTE example: 

<pre><code class="code-10 nolinks" id="code-20-43077314-1">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 &lt;&gt; detail_total
;
</code></pre>
</div>

pclarke7

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

<div>
<div class="content wysiwyg-content">
Hello, 
I have two simple tables Header &amp;&nbsp;Detail 
 
Header 
Code &nbsp; &nbsp; Quantity 
AAA &nbsp; &nbsp; &nbsp; &nbsp;10 
AAA &nbsp; &nbsp; &nbsp; &nbsp;20 
AAA &nbsp; &nbsp; &nbsp; &nbsp;30 
 
Detail &nbsp; 
Code &nbsp; &nbsp; Quantity 
AAA &nbsp; &nbsp; &nbsp; 5 
AAA &nbsp; &nbsp; &nbsp; 5 
AAA &nbsp; &nbsp; &nbsp; 10 
AAA &nbsp; &nbsp; &nbsp; 10 
AAA &nbsp; &nbsp; &nbsp; 10 
AAA &nbsp; &nbsp; &nbsp; 15 
AAA &nbsp; &nbsp; &nbsp; 5 
 
I need a SQL statement which will &nbsp;validate that the Total for Code AAA on the header table &nbsp;matches the Total for Code AAA on the detail table 
 
&nbsp; SELECT a.code AS &quot;Code&quot;, &nbsp;SUM(a.quantity ) AS &quot;Header Total&quot;, SUM(b.quantity) AS &quot;Detail Total&quot; 
&nbsp; FROM Header a, Detail b WHERE a.code=b.code 
&nbsp; GROUP BY a.code 
 
However I am getting the following for AAA 
 
Code &nbsp; Header Total &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Detail Total 
AAA &nbsp; &nbsp; &nbsp; &nbsp; 420 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;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 &nbsp; Header Total &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Detail Total 
AAA &nbsp; &nbsp; &nbsp; &nbsp; 60 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60
</div>
</div>

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

INVALID SUM RESULTS from SQL JOIN

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Oracle Database

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.