Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: How do i combine selected columns (AKA coding around bad data)

    I have a standard query, that returns head counts for units of my company. The problem is that "some" of the units should now be combined because the data I am using is legacy.

    So while now I get something like this :

    SELECT SUM(unit) as headcount, unit
    FROM myTable

    Unit / headcount
    aa / 10
    bb / 20
    cc / 30
    dd / 40

    But I need to combine unit 'aa' and 'cc' (which i will rename as 'ac'). So my query should output this :

    ac / 40
    bb / 20
    dd / 40

    I essentially need to hard-code the combining rules in my query. Is this possible?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps using DECODE or CASE?

  3. #3
    Join Date
    Jan 2004
    Posts
    83
    i haven't used decode or case before. i'm reading up on them now. but, in the examples i'm looking at it seems that i would have to hard code all my units, as opposed to only a few.

    i don't want to have to do this :

    case when unit = aa then 1
    case when unit = bb then 1

    i'd like to do something like "if unit = aa or cc, then combine, otherwise, count and group everything else"

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11

    Thumbs up

    Not sure if you are summing or counting units, but....
    Code:
    select sum(case when unit = 'aa' or unit = 'cc' then unitheadcount
    else unitheadcount end) as headcount, 
    case when unit = 'aa' or unit = 'cc' then 'ac' 
    else unit end as unit
    FROM myTable
    group by case when unit = 'aa' or unit = 'cc' then 'ac' 
    else unit end
    Last edited by MCrowley; 01-16-06 at 11:04.

  5. #5
    Join Date
    Jan 2004
    Posts
    83
    I see you are using GROUP BY CASE. do i have to have a case statement in my select statment, or could i just have my query as it is now, and then add a GROUP BY CASE...(which i couldn't get to work)

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by turk99
    ... it seems that i would have to hard code all my units ...
    Well, if you DON'T hard code those values, how is Oracle supposed to know that you want to combine 'aa' with 'cc'?

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    You could reduce the number of times you have to repeat the CASE/DECODE expression with something like this (untested):

    Code:
    SELECT unit
         , SUM(unitheadcount)
    FROM   ( SELECT CASE
                       WHEN unit IN('aa','cc') THEN 'ac'
                       ELSE unit
                    END AS unit
                  , unitheadcount
             FROM   mytable )
    GROUP BY unit;

  8. #8
    Join Date
    Jan 2004
    Posts
    83
    Quote Originally Posted by MCrowley
    Not sure if you are summing or counting units, but....
    Code:
    select sum(case when unit = 'aa' or unit = 'cc' then unitheadcount
    else unitheadcount end) as headcount, 
    case when unit = 'aa' or unit = 'cc' then 'ac' 
    else unit end as unit
    FROM myTable
    group by case when unit = 'aa' or unit = 'cc' then 'ac' 
    else unit end
    This was exactly what I needed. Thank you.

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by MCrowley
    Code:
    case when unit = 'aa' or unit = 'cc' then unitheadcount
    else unitheadcount end
    Isn't that the same as
    Code:
    unitheadcount
    ?

Posting Permissions

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