Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
3 replies Latest Post - ‏2012-04-18T15:59:03Z by SystemAdmin
VPadala
VPadala
1 Post
ACCEPTED ANSWER

Pinned topic Need help in small query

‏2012-04-15T22:00:07Z |
I have two columns in my table. I need sum the amounts if code is 'C' and I need subtract if code is 'D'.
Please help me writing this query

Amt code

100 C
200 C
300 C
300 D

desired out put is 300
Updated on 2012-04-18T15:59:03Z at 2012-04-18T15:59:03Z by SystemAdmin
  • ocgstyles
    ocgstyles
    472 Posts
    ACCEPTED ANSWER

    Re: Need help in small query

    ‏2012-04-16T13:03:44Z  in response to VPadala
    Here's one way...

    with c as (
    select sum(amt) amt
    from padala
    where code = 'C'
    group by code
    ), d as (
    select sum(amt) amt
    from padala
    where code = 'D'
    group by code
    )
    select c.amt - d.amt as amt
    from c, d;
    • Keith
  • SystemAdmin
    SystemAdmin
    5837 Posts
    ACCEPTED ANSWER

    Re: Need help in small query

    ‏2012-04-18T10:16:47Z  in response to VPadala
    Here is another:
    
    SELECT SUM(CASE CODE_COL WHEN 
    'C' THEN  1 ELSE 0 END * AMT_COL) - SUM(CASE CODE_COL WHEN 
    'D' THEN -1 ELSE 0 END * AMT_COL) FROM table-name WHERE ...
    
    • SystemAdmin
      SystemAdmin
      5837 Posts
      ACCEPTED ANSWER

      Re: Need help in small query

      ‏2012-04-18T15:59:03Z  in response to SystemAdmin
      Another ways:

      select
      (select sum(amount) from table where code = 'D') -
      (select sum(amount) from table where code = 'C')
      from sysibm.sysdummy1

      If amount can be null:

      select
      (select sum(coalesce(amount, 0)) from table where code = 'D') -
      (select sum(coalesce(amount, 0)) from table where code = 'C')
      from sysibm.sysdummy1

      Oracle mode if amount can be null:

      select
      (select sum(nvl(col, 0)) from table where code = 'D') -
      (select sum(nvl(col, 0)) from table where code = 'C')
      from dual

      If cant be null, shorter with decode (did db2 haves decode function?)

      select sum(decode(code, 'D', amount, amount * -1))
      from table