Re: SQL Question, taking differences between sum of columns

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Wed, 12 Jul 2006 12:57:55 GMT
Message-ID: <J2AK0t.2x3@igsrsparc2.er.usgs.gov>

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

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Wed Jul 12 2006 - 07:57:55 CDT