trouble with subquery

ghall202 · July 9th, 2008, 10:51 AM

I have the following qry:
SELECT tblDlr_Data.bkyr AS bookyr, Sum(tblDlr_Data.original_amtfin) AS original_amtfin, Sum(tblDlr_Data.EOM_ENR) AS EOM_ENR, (select sum([eom_enr]) from [tblDlr_Data] where [deldynb]="3" ) AS Expr1
FROM tblDlr_Data
GROUP BY tblDlr_Data.bkyr;

bkyr contains several rows (2003, 2004, 2005, etc).

Expr1 is bringing back the same total [eom_enr] where [deldynb] is equal to "3" in each row rather than grouping by bkyr. I've tried to us a "group by" within the subquery but that is not working. Any suggestions?

Old Pedant · July 9th, 2008, 01:55 PM

Avoid the subselect...it's not needed, it's a bad idea, and in any case it's not complex enough to get you the results you are after.

And why would you compare to "3" instead of just 3??? Is deldynb *really* a text field instead of a number field?

Try this:

Code:

SELECT bkyr AS bookyr, 
       Sum(original_amtfin) AS original_amtfin, 
       Sum(EOM_ENR) AS EOM_ENR, 
       Sum( IIF( deldynb=3, EOM_ENR, 0 ) ) AS whatever
FROM tblDlr_Data
GROUP BY bkyr
ORDER BY bkyr