Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2012
    Posts
    3

    Unanswered: Case statements and SUM?

    Hi,

    I have some SQL which uses CASE to translate a character into a word and a count of the number of occurances. Unfortunately, as there are more than one character which can be translated into a word I end up with multiple lines in my return.

    My current SQL
    Code:
    select distinct (case when dbms_lob.substr(msg_body,1,4) in ('A','B','C','E') then 'BILL'
    when dbms_lob.substr(msg_body,1,4) ='F' then 'BOB'
    when dbms_lob.substr(msg_body,1,4) ='G' then 'JOHN'
    when dbms_lob.substr(msg_body,1,4) ='H' then 'TOM'
    when dbms_lob.substr(msg_body,1,4) ='I' then 'NIGEL'
    end) as NetworkId,
    A_STATUS, count(*) from A_MESSAGE_LOG 
    where CREATION_TIME like '16-OCT-12%'
    group by dbms_lob.substr(msg_body,1,4), A_STATUS
    order by NetworkId
    This returns:

    Code:
    BILL   A   2132
    BILL   D   44
    BILL   D   99
    BILL   G   12
    BOB   A    2555
    JOHN A    3000
    JOHN G    65
    TOM  A    1000
    NIGEL G    546
    What I want:
    Code:
    BILL   A   2132
    BILL   D   143         (the total count!)
    BILL   G   12
    BOB   A    2555
    JOHN A    3000
    JOHN G    65
    TOM  A    1000
    NIGEL G    546
    Does anyone know the best way to achieve this, do I need to be thinking about nesting SUM or something in my SQL?

    Thanks

    Chris

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Are the two 'D' statuses somehow different? What do you get when you run
    Code:
     select A_STATUS, count(*)
    from A_MESSAGE_LOG
    group by A_STATUS

  3. #3
    Join Date
    Oct 2012
    Posts
    3
    If I did, that it would return somthing like:

    Code:
    A_STATUS         count(*)
    --------------------------
    A                     8500
    D                     500
    G                     600
    If I select a count by the character I convert into NetworkId I'd get something like:

    Code:
    dbms_lob.substr(msg        count(*)
    ----------------------------------
    A                                  213
    B                                  1000
    C                                  200 
    E                                  2333
    F                                  1231
    G                                  24
    I                                   213

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Ahh, I see the problem, now. You are grouping on dbms_lob.substr(msg_body,1,4), you should group on the entire case statement.

  5. #5
    Join Date
    Oct 2012
    Posts
    3
    Thanks, but excuse my ignorance. Is there a quick way of referencing the case statement in the group by or do I have to reiterate the whole statement?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You can try to reference the column alias (NetworkID), but most likely you will have to reiterate the whole case statement.

Posting Permissions

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