Group SQL Select Statement

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,

AndreasSomething 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@.ny.frb.orgwrote in message
news:1174673610.080198.22590@.y66g2000hsf.googlegro ups.com...

Quote:

Originally Posted by

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
>

|||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...@.gmail.comwrote:

Quote:

Originally Posted by

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.strzo...@.ny.frb.orgwrote in message
>
news:1174673610.080198.22590@.y66g2000hsf.googlegro ups.com...
>
>
>

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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')))

>

Quote:

Originally Posted by

Thanks,

>

Quote:

Originally Posted by

Andreas- Hide quoted text -

>
- Show quoted text -

|||Andreas wrote:

Quote:

Originally Posted by

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