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

turk99 · #1 (**permalink**) 01-16-06, 09:07

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?

Littlefoot · #2 (**permalink**) 01-16-06, 09:09

Perhaps using DECODE or CASE?

turk99 · #3 (**permalink**) 01-16-06, 09:45

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"

MCrowley · #4 (**permalink**) 01-16-06, 10:00

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

turk99 · #5 (**permalink**) 01-16-06, 10:31

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)

Littlefoot · #6 (**permalink**) 01-17-06, 00:06

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'?

WilliamR · #7 (**permalink**) 01-17-06, 03:39

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;

turk99 · #8 (**permalink**) 01-18-06, 13:41

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.

WilliamR · #9 (**permalink**) 01-21-06, 10:07

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

?

Internet Services

Web Development

Digital Marketing

Consumer Tech