Posted by Ed Murphy on 03/24/07 02:14

Andreas wrote:

> MS Query tells me ORA-00979: not a GROUP BY expression. Are there only
> certain fields that I can group by?

Did you add any fields to the SELECT line? If so, then you need to
also add them to the GROUP BY line.

MC's query looks correct to me, though I would personally link the
tables using JOIN instead of WHERE, and assign a field name to the
computed total:

select
SCDMASTER.SCTY_CLASS_CODE,
sum(CUSTODY_BALANCE.OPENING_BALANCE
+ CUSTODY_BALANCE.DEPOSIT_AMOUNT
- CUSTODY_BALANCE.WITHDRAWAL_AMOUNT) as TotalBalance
from PMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE
join CAPSREPORT.SCDMASTER SCDMASTER
on CUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID
where CUSTODY_BALANCE.APPLICATIONCYCLEDATE = {ts '2007-03-21 00:00:00'}
and SCDMASTER.SCTY_CLASS_CODE not in (
'BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN','CA','YD'
)
group by SCDMASTER.SCTY_CLASS_CODE

[Back to original message]