Re: SQL Question, taking differences between sum of columns

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 12 Jul 2006 14:07:15 -0700
Message-ID: <1152738435.806769.174650@m79g2000cwm.googlegroups.com>

Brian Peasland wrote:
> Charles Hooper wrote:
> > 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)
> >>
> >>
>
> > 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;
>
> I doubt the above will work. You should get results similar to this:
>
> MY_SUM
> ------
> 10
> 23
> 22
> 21
> -43
> 0
> 0
> 0
>
> Something more on the order of this should work:
>
>
> SELECT g1.g1_val + g2.g2_val - g3.g3_val
> FROM (SELECT SUM(val) AS G1_VAL FROM my_table WHERE type='G1') g1,
> (SELECT SUM(val) AS G2_VAL FROM my_table WHERE type='G2') g2,
> (SELECT SUM(val) AS G3_VAL FROM my_table WHERE type='G3') g3;
>
>
> HTH,
> Brian
>
>
> --
> ===================================================================
>
> Brian Peasland
> dba_at_nospam.peasland.net
> http://www.peasland.net

Maybe we are using different optimizer goals (your method will require 3 times the IO)? Maybe different versions of Oracle? :-) There is a lot of compound code in my SQL statement, and it is easy to overlook the SUM( ). Here is an example run of the SQL statement in my live database. The sample as originally written does work as expected.

SELECT NVL(SUM(DECODE(RESOURCE_ID,'91',HOURS_WORKED,0)),0)+NVL(SUM(DECODE(RESOURCE_ID,'100',HOURS_WORKED,0)),0)-NVL(SUM(DECODE(RESOURCE_ID,'54',HOURS_WORKED,0)),0) MY_SUM
FROM
LABOR_TICKET; MY_SUM

-19495.96

SELECT
COUNT(*)
FROM
LABOR_TICKET; COUNT(*)

1167711

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Jul 12 2006 - 16:07:15 CDT