Hi,

I am bloody amateure and I was wondering if someone could help me edit
the statement below so it groups the field SCDMASTER.SCTY_CLASS_CODE
and creates one field called "total balance" equalling
CUSTODY_BALANCE.OPENING_BALANCE + CUSTODY_BALANCE.DEPOSIT_AMOUNT -
CUSTODY_BALANCE.WITHDRAWAL_AMOUNT

SELECT
CUSTODY_BALANCE.APPLICATIONCYCLEDATE,
CUSTODY_BALANCE.ASSET_ID,
SCDMASTER.SCTY_CLASS_CODE,
CUSTODY_BALANCE.OPENING_BALANCE,
CUSTODY_BALANCE.DEPOSIT_AMOUNT,
CUSTODY_BALANCE.WITHDRAWAL_AMOUNT
FROM
FPMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,
CAPSREPORT.SCDMASTER SCDMASTER
WHERE
CUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND
((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')))

Thanks,

Andreas

Something like this=

Select
SCDMASTER.SCTY_CLASS_CODE,
sum(CUSTODY_BALANCE.OPENING_BALANCE +
CUSTODY_BALANCE.DEPOSIT_AMOUNT -CUSTODY_BALANCE.WITHDRAWAL_AMOUNT)

from
PMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,
CAPSREPORT.SCDMASTER SCDMASTER
where
CUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND
((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



MC

"Andreas" <andreas.strzodka (AT) ny (DOT) frb.org> wrote

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


On Mar 23, 2:22 pm, "MC" <marko.NOSPAMc... (AT) gmail (DOT) com> wrote:

Andreas wrote:

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