Home Forums Programming General How do you do a GROUP BY on a value that's changed by a CASE? RE: How do you do a GROUP BY on a value that's changed by a CASE?

  • April 3, 2014 at 3:40 pm

    #1703115

    You have 2 options:

    First one:

    WITH CTE AS(

    select case atr.Race

    when 32 then 'White'

    when 1 then 'Black/African American'

    when 2 then 'Asian'

    when 4 then 'American Indian/Alaskan Native'

    when 16 then 'American Indian/Alaskan Native'

    when 8 then 'Native Hawaiian/Other Pacific Islander'

    when 36 then 'American Indian/Alaskan Native and White'

    when 48 then 'American Indian/Alaskan Native and White'

    when 34 then 'Asian and White'

    when 33 then 'Black/African American and White'

    when 5 then 'American Indian/Alaskan Native and Black/African American'

    when 17 then 'American Indian/Alaskan Native and Black/African American'

    when 10 then 'Asian/Pacific Islander'

    else 'Other Multi-Racial'

    end as RaceDescription,

    yesno.YesNoDesc as 'Hispanic Yes/No'

    from dbo.asigeneral as asi

    inner join dbo.asiatrsupplement as atr

    on atr.clientnumber = asi.clientnumber

    and atr.casenumber = asi.casenumber

    left join dbo.ASICodesATRYesNo yesno

    on atr.hispanicyesno = yesno.YesNo

    where asicompleted between '2014-1-1' and '2014-3-31'

    )

    SELECT RaceDescription,

    YesNoDesc,

    count(*) as racetotal

    FROM CTE

    group by race, YesNoDesc

    order by race, YesNoDesc desc

    2nd option:

    select case atr.Race

    when 32 then 'White'

    when 1 then 'Black/African American'

    when 2 then 'Asian'

    when 4 then 'American Indian/Alaskan Native'

    when 16 then 'American Indian/Alaskan Native'

    when 8 then 'Native Hawaiian/Other Pacific Islander'

    when 36 then 'American Indian/Alaskan Native and White'

    when 48 then 'American Indian/Alaskan Native and White'

    when 34 then 'Asian and White'

    when 33 then 'Black/African American and White'

    when 5 then 'American Indian/Alaskan Native and Black/African American'

    when 17 then 'American Indian/Alaskan Native and Black/African American'

    when 10 then 'Asian/Pacific Islander'

    else 'Other Multi-Racial'

    end as RaceDescription,

    yesno.YesNoDesc as 'Hispanic Yes/No',

    count(*) as racetotal

    from dbo.asigeneral as asi

    inner join dbo.asiatrsupplement as atr

    on atr.clientnumber = asi.clientnumber

    and atr.casenumber = asi.casenumber

    left join dbo.ASICodesATRYesNo yesno

    on atr.hispanicyesno = yesno.YesNo

    where asicompleted between '2014-1-1' and '2014-3-31'

    group by case atr.Race

    when 32 then 'White'

    when 1 then 'Black/African American'

    when 2 then 'Asian'

    when 4 then 'American Indian/Alaskan Native'

    when 16 then 'American Indian/Alaskan Native'

    when 8 then 'Native Hawaiian/Other Pacific Islander'

    when 36 then 'American Indian/Alaskan Native and White'

    when 48 then 'American Indian/Alaskan Native and White'

    when 34 then 'Asian and White'

    when 33 then 'Black/African American and White'

    when 5 then 'American Indian/Alaskan Native and Black/African American'

    when 17 then 'American Indian/Alaskan Native and Black/African American'

    when 10 then 'Asian/Pacific Islander'

    else 'Other Multi-Racial'

    end, YesNoDesc

    order by race, YesNoDesc desc

    Note that atr.Race was removed from the column list to be able to group by the descriptions.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2