Group by Question

select 
        case status
   开发者_C百科         when 'AR' then 1
            when 'AV' then 2
            when 'AN' then 3
            when 'AD' then 4
            when 'AC' then 5
            when 'AY' then 6
            when 'AH' then 7
            else 8
        end 'status_order', COUNT(*)
from dn_appeal_request
group by 
        case status
            when 'AR' then 1
            when 'AV' then 2
            when 'AN' then 3
            when 'AD' then 4
            when 'AC' then 5
            when 'AY' then 6
            when 'AH' then 7
            else 8
        end 

is there a way to do a group by without using the same case statement used in the select?

You can avoid repeating your case statement by using a sub-query. You do need to group on the value.

select D.status_order, COUNT(*)
from (
        select case status
                 when 'AR' then 1
                 when 'AV' then 2
                 when 'AN' then 3
                 when 'AD' then 4
                 when 'AC' then 5
                 when 'AY' then 6
                 when 'AH' then 7
                 else 8
               end 'status_order'
        from dn_appeal_request
     ) as D
group by D.status_order

No, there's no way to avoid the CASE in the GROUP BY clause, in your sample above.

If you absolutely did not want to repeat yourself, a workaround you could do is to create a derived table:

 SELECT CASE [Status]   when 'AR' then 1            
        when 'AV' then 2            
        when 'AN' then 3            
        when 'AD' then 4            
        when 'AC' then 5            
        when 'AY' then 6            
        when 'AH' then 7            
        else 8        
        END AS status_order
        ,Total
 FROM (
     SELECT [Status], COUNT(*) AS Total
     FROM dn_appeal_request
     GROUP BY  [Status]
 ) AS Foo