At 12:30 AM 11/04/2007, you wrote:

>Hi..
>
>In this specific instance I'm working with two tables, and trying to
>get some aggregate information out of one of them.
>
>The two tables are header and detail. The primary key of header is
>header_auto_key, and that appears in the detail table as a foreign key.
>
>When I run the query: SELECT sum(total_price) FROM header; I get a
>correct result.
>
>If I run: SELECT sum(total_price) FROM header, detail WHERE
>header.header_auto_key = detail.header_auto_key; I get an incorrect
>result.
>
>With the last example, duplicate values (total_price) are returned
>from the header table. The total_price field does not exist in the
>detail table.
>
>It's a long drawn out story, why I would need to run a query like the
>second option. But I thought someone might have a suggestion as to how
>I could restructure that second query, to filter out duplicate results..

Well, the basic cause of course is that SUM() is an aggregate
function and you have no aggregation specified - so the aggregation
is being performed for every eligible row in the output, i.e. every
concurrence of the header and detail.

As you admit, the presence of the detail stream in this query appears
nonsensical. At a guess, you want it there as an exclusion mechanism
because of some questionable design decisions to store the detail
totals in the header record along with some trigger or (worse!)
external program that is setting header.total_price to zero whenever
it is null. It ought to be null on headers that have no details and
zero only on headers WITH details that aggregate to zero....

Assuming you want you want one sum for the whole table, try using a
proper existence test as your mechanism for excluding headers that
have no details:

SELECT
h.header_auto_key,
sum(h.total_price) FROM header h
where exists (
select 1 from detail d
where h.header_auto_key = d.header_auto_key)

However, you probably don't need the existence test at all. SUM()
simply bypasses any records that are null, so the nett result is the
same whether the total_price is null or zero.
===================================
On the subject of those creaky old implicit inner joins, get rid of
them. They are a thing of the distant past, superseded 20 years ago
for good reasons. One of the benefits of the explicit syntax is that
it's a lot easier to discover where you've stuffed up in joins (and
harder to stuff up to start with!)

You are also creating logical ambiguity whenever you don't qualifying
*all* field references in joins, a practice which is either
"foolhardy" or "verboten", depending on how high up the upgrade chain
you are, i.e. if it's not biting you now with an exception then it's
waiting to bite you when you upgrade.

But that's joins...you don't need or want one here.

./heLen