Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Question, taking differences between sum of columns
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
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
1167711
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Wed Jul 12 2006 - 16:07:15 CDT