Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Question Unanswered: need help with a query

    In the below query ,if there is no row with ORIG_TERM_IND as 'T' the whole query fails ,even though there are rows with value as 'O'.

    What I want is that CALLING should display the sum of those rows which have ORIG_TERM_IND as 'O' and CALLED should display 0

    SELECT SUM(E1.DUR) CALLING,
    SUM(E2.DUR) CALLED
    FROM
    Table a E1,
    Table a E2
    WHERE
    E2.LOGIN=E1.LOGIN
    AND E1.ORIG_TERM_IND = 'O'
    AND E2.ORIG_TERM_IND ='T'
    GROUP BY
    E1.LOGIN

    Thanks in advance

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    21
    I am not sure whether I understand your porblem but try this code...it may help you...

    SELECT SUM(E1.DUR) CALLING,
    SUM(E2.DUR) CALLED
    FROM
    Table a E1,
    Table a E2
    WHERE
    E2.LOGIN=E1.LOGIN
    AND
    (E1.ORIG_TERM_IND = 'O' OR E2.ORIG_TERM_IND ='T')
    GROUP BY
    E1.LOGIN
    wuadko

  3. #3
    Join Date
    Mar 2004
    Posts
    4
    Sorry ,but that wont help becuase by putting OR both CALLING and CALLED would display the same values .
    I want CALLING to display sum of those rows whihc have value as 'O' and CALLED to display sum of those rows which have value as 'T'.


    Originally posted by wuadko
    I am not sure whether I understand your porblem but try this code...it may help you...

    SELECT SUM(E1.DUR) CALLING,
    SUM(E2.DUR) CALLED
    FROM
    Table a E1,
    Table a E2
    WHERE
    E2.LOGIN=E1.LOGIN
    AND
    (E1.ORIG_TERM_IND = 'O' OR E2.ORIG_TERM_IND ='T')
    GROUP BY
    E1.LOGIN

  4. #4
    Join Date
    Mar 2004
    Location
    Venice,Italy
    Posts
    20

    Re: need help with a query

    I'm new of here, so I take this to present myself: I'm from Italy and I worked mainly on oracle,mssql and mysql

    a better solution that query the table once can be this (I assume you use oracle,but it can be translate easily)

    select
    sum(decode(ORIG_TERM_IND,'O',DUR,0)) CALLING,
    sum(decode(ORIG_TERM_IND,'T',DUR,0)) CALLED
    from table
    group by login

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    zeus77's approach written in standard sql --
    PHP Code:
    select login
         
    sum( case when ORIG_TERM_IND 'O'
                     
    then DUR
                     
    else 0   end) as CALLING
         
    sum( case when ORIG_TERM_IND 'T'
                     
    then DUR
                     
    else 0   end) as CALLED
      from table
    group 
        by login 
    another approach:
    PHP Code:
    select login
         
    'Calling' as type
         
    sum(DUR) as sumtype
      from table
     where ORIG_TERM_IND 
    'O'  
    group 
        by login
    union all
    select login
         
    'Called' 
         
    sum(DUR
      
    from table
     where ORIG_TERM_IND 
    'T'  
    group 
        by login 
    yet another approach:
    PHP Code:
    select login
         
    ORIG_TERM_IND 
         
    sum(DUR) as sumtype
      from table
    group 
        by login
         
    ORIG_TERM_IND 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Location
    Venice,Italy
    Posts
    20
    I didn't know 'case when then else' is in standard SQL.... good to know, thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •