Group SQL Select Statement

[Andreas]

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

[MC]

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" <an**************@ny.frb.orgwrote in message
news:11*********************@y66g2000hsf.googlegro ups.com...

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

[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" <[e-mail]:

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" <[e-mail] in message

news:11*********************@y66g2000hsf.googlegro ups.com...

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- Hide quoted text -

- Show quoted text -

[Ed Murphy]

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