Re: SQL Question, taking differences between sum of columns

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 12 Jul 2006 03:01:28 -0700
Message-ID: <1152698488.611831.123350@i42g2000cwa.googlegroups.com>

Anil G wrote:
> Hi,
>
> I have following table structure, tbl1( type_code varchar2(100), val
> integer)
> having data as:
> Type Val
> --------- --------------
> G1 10
> G1 23
> G2 22
> G2 21
> G3 43
> G4 11
> G4 33
> G5 44
>
> I would like to perform calculation such as ((sum(G1) + sum(G2)) -
> sum(G3)
>
> And further more ((sum(G1) + sum(G2)) - sum(G3) * sum(G4)
>
>
> Any help would be appriciated,
>
> Thanks,
> Anil G

Something like this should work:
SELECT NVL(SUM(DECODE(TYPE_CODE,'G1',VAL,0)),0)+NVL(SUM(DECODE(TYPE_CODE,'G2',VAL,0)),0)-NVL(SUM(DECODE(TYPE_CODE,'G3',VAL,0)),0) MY_SUM
FROM
TBL1; You should be able to use the DECODE function to break this apart: DECODE(TYPE_CODE,'G1',VAL,0) When the value of TYPE_CODE is equal to 'G1', return the value of VAL, otherwise return 0. NVL is included just as a precaution to prevent nulls.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Jul 12 2006 - 05:01:28 CDT