Grouping by case field

Question

so I'm pretty new to SQL and almost wholly self-taught so far. I'm currently trying to run some analysis on the ranks of staff, where I'm trying to "reclassify" about 15+ ranks to 4 rank categories. Then I want to group by those categories, but am running into some trouble (and SQL for Dummies seems to have run out of steam, or I can't find the answer...).

Anyway, the query I have at the moment is as follows. This gives the error that the group by must contain one column that isn't an outer reference, which I understand, but don't know how to workaround.

EDIT: I'm aiming to get a result that gives me the following:

RANK                      Hours etc
Equity Partner            12
Fixed Share Partner       20
Associate                 50
Trainee                   25
Other                     15

Any help would be greatly appreciated Matt

declare @startperiod as integer
declare @endperiod as integer
declare @offc as integer
declare @dept as varchar(3)

select @startperiod = '201101'
select @endperiod = '201112'
select @offc = '55'

select 
    case k.rank_code 
                when '10'   then 'Equity Partner'
                when '110'  then 'Fixed Share Partner'
                when '130'  then 'Associate'
                when '131'  then 'Associate' 
                When '132'  then 'Associate'
                when '133'  then 'Associate'
                when '134'  then 'Associate'
                when '135'  then 'Associate'
                when '136'  then 'Associate'
                when '137'  then 'Associate'
                when '141'  then 'Associate'
                when '142'  then 'Associate'
                when '341'  then 'Trainee'
                when '342'  then 'Trainee'
                else 'Other'
end as 'Rank Desc',
sum(b.base_hrs) as 'Base Hrs',sum(b.tobill_hrs) as 'ToBill Hrs',sum(b.billed_hrs) as 'Billed Hrs', 
sum(b.base_amt) as 'Base Amt',sum(b.tobill_amt) as 'ToBill Amt',sum(b.billed_amt) as 'Billed Amt'

from blh_billed_fees b, tbl_rank k, tbm_persnl p
where b.tk_empl_uno = p.empl_uno
and p.rank_code = k.rank_code

group by 'Rank Desc'

aF. · Accepted Answer · 2012-05-09T15:18:07.283

You can't do a group by with a column defined in the current select.

Make this select a sub-query and then you can use the column RankDesc to make the group by.

The final query should be something like this:

select 
aux.RankDesc as 'Rank Desc',
sum(aux.base_hrs) as 'Base Hrs',
sum(aux.tobill_hrs) as 'ToBill Hrs',
sum(aux.billed_hrs) as 'Billed Hrs', 
sum(aux.base_amt) as 'Base Amt',
sum(aux.tobill_amt) as 'ToBill Amt',
sum(aux.billed_amt) as 'Billed Amt'

from
    (select 
        case k.rank_code 
                    when '10'   then 'Equity Partner'
                    when '110'  then 'Fixed Share Partner'
                    when '130'  then 'Associate'
                    when '131'  then 'Associate' 
                    When '132'  then 'Associate'
                    when '133'  then 'Associate'
                    when '134'  then 'Associate'
                    when '135'  then 'Associate'
                    when '136'  then 'Associate'
                    when '137'  then 'Associate'
                    when '141'  then 'Associate'
                    when '142'  then 'Associate'
                    when '341'  then 'Trainee'
                    when '342'  then 'Trainee'
                    else 'Other'
    end as 'RankDesc',
    b.base_hrs,
    b.tobill_hrs,
    b.billed_hrs, 
    b.base_amt,
    b.tobill_amt,
    b.billed_amt

    from blh_billed_fees b, tbl_rank k, tbm_persnl p
    where b.tk_empl_uno = p.empl_uno
    and p.rank_code = k.rank_code) aux
group by aux.RankDesc

like @PhilipKelley stated, you can also include the case on the group by part, and with that you wouldn't need to use the sub-query.

aF - many thanks - that seems to have cracked it. I think I intuitively understand what's going on (I get why mine wouldn't work for definite), but I'll have a good pick through to make sure I get it. — mattknight1986, May 09 '12 at 14:34
You could also include the entire case statement in the group by clause, but given the size and complexity, this is a lot clearer. — Philip Kelley, May 09 '12 at 14:36
@mattknight1986 like Philip stated, you can also include the case on the group by part, and with that you wouldn't need to use the sub-query. — aF., May 09 '12 at 15:17

score 0 · Answer 2 · answered May 09 '12 at 14:28

0

Either make it a subquery, and you group by its resulting fields, or (arguably better yet if it's reusable enough) create a view and group by its fields.

answered May 09 '12 at 14:28

Blindy

65,249
10
91
131

score 0 · Answer 3 · answered May 09 '12 at 14:33

0

the select with case has to be a subquery this makes it appear as a table when the paarent executes and grouping can then be applied on it with ease.

answered May 09 '12 at 14:33

Amogh Rai

159
1
7

Grouping by case field

3 Answers3