Board index » delphi » SQL - aggregate functions and groups

SQL - aggregate functions and groups

If I have two tables:

Master

M_ID
M_DESC

and Detail

D_ID
D_MASTER (= M_ID)
D_AMOUNT

and I want to get a total for each master, I would code the query as

SELECT M_DESC, M_ID, SUM(D_AMOUNT) AS TOTAL FROM DETAIL
JOIN MASTER ON M_ID=D_MASTER
GROUP BY M_ID

Seems to work fine in most cases, but one of our database vendors insists
that that syntax is illegal under SQL-92 and that the result of the SUM
function can not be trusted to be accurate. Is he right? (I hope not: we
have this format query all over the place)

Robert

 

Re:SQL - aggregate functions and groups


The use of JOIN is not SQL-92 compliant. You should use:

SELECT MASTER.M_DESC, MASTER.M_ID, SUM(DETAIL.D_AMOUNT) AS TOTAL
FROM MASTER, DETAIL
WHERE MASTER.M_ID=DETAIL.D_MASTER
GROUP BY MASTER.M_ID

ps. The SUM function will always be accurate (as long as JOIN is supported).

"Robert Kaplan" <rkap...@iamerica.net> schreef in bericht
news:h_0I5.154$Lk5.5941@dca1-nnrp2.news.digex.net...

Quote
> If I have two tables:

> Master

> M_ID
> M_DESC

> and Detail

> D_ID
> D_MASTER (= M_ID)
> D_AMOUNT

> and I want to get a total for each master, I would code the query as

> SELECT M_DESC, M_ID, SUM(D_AMOUNT) AS TOTAL FROM DETAIL
> JOIN MASTER ON M_ID=D_MASTER
> GROUP BY M_ID

> Seems to work fine in most cases, but one of our database vendors insists
> that that syntax is illegal under SQL-92 and that the result of the SUM
> function can not be trusted to be accurate. Is he right? (I hope not: we
> have this format query all over the place)

> Robert

Re:SQL - aggregate functions and groups


Quote
M.H. Avegaart <avegaartNOS...@mccomm.nl> wrote in message

news:8t0q14$qmd$1@porthos.nl.uu.net...

Quote
> The use of JOIN is not SQL-92 compliant. You should use:

> SELECT MASTER.M_DESC, MASTER.M_ID, SUM(DETAIL.D_AMOUNT) AS TOTAL
> FROM MASTER, DETAIL
> WHERE MASTER.M_ID=DETAIL.D_MASTER
> GROUP BY MASTER.M_ID

> ps. The SUM function will always be accurate (as long as JOIN is
supported).

That's what I thought. The makers of DBISAM say otherwise, though I suspect
it was more one of those "it's not a bug, it's a feature" responses. Go
figure. We need an SQL police to enforce the rules :-)

Robert

Quote

> "Robert Kaplan" <rkap...@iamerica.net> schreef in bericht
> news:h_0I5.154$Lk5.5941@dca1-nnrp2.news.digex.net...
> > If I have two tables:

> > Master

> > M_ID
> > M_DESC

> > and Detail

> > D_ID
> > D_MASTER (= M_ID)
> > D_AMOUNT

> > and I want to get a total for each master, I would code the query as

> > SELECT M_DESC, M_ID, SUM(D_AMOUNT) AS TOTAL FROM DETAIL
> > JOIN MASTER ON M_ID=D_MASTER
> > GROUP BY M_ID

> > Seems to work fine in most cases, but one of our database vendors
insists
> > that that syntax is illegal under SQL-92 and that the result of the SUM
> > function can not be trusted to be accurate. Is he right? (I hope not: we
> > have this format query all over the place)

> > Robert

Other Threads