SQL - CASE statement with Group By function

I wrote this query and trying to also group b开发者_C百科y "D" but it is not working. Any help??

(select hr_id, max(delivery_dt)as maxd,
  (CASE 
    WHEN max(delivery_dt) > '11-may-2010' then '< 6 MO'
    WHEN max(delivery_dt) > '11-may-2004' and  max(delivery_dt) < '11-may-2010'   then '7 - 78 MO'
    WHEN max(delivery_dt) > '11-nov-1999' and  max(delivery_dt) < '11-april-2004' then '79 - 132 MO'
    ELSE '> 133 MO' END) D
from INDIVIDUAL_VE_TB 
where hr_id in (select distinct hr_id from MODEL_SC_TB b where b.model_id='200')
Group by hr_id)

i got this result

HR                DATE               D
2000001076        4/22/1994         > 133 MO
2000004986        12/13/2004        7 - 78 MO
2000003382        12/13/2003        7 - 78 MO

this is grouping by HR, i am also trying to group by D but it is not working I added D to the group by Group by hr_id, D

Any help would be great!!!

Thank you in advance

You could try:

Select hr_id, max(maxd), D
from
    (select hr_id, max(delivery_dt)as maxd,
      (CASE 
        WHEN max(delivery_dt) > '11-may-2010' then '< 6 MO'
        WHEN max(delivery_dt) > '11-may-2004' and  max(delivery_dt) < '11-may-2010'   then '7 - 78 MO'
        WHEN max(delivery_dt) > '11-nov-1999' and  max(delivery_dt) < '11-april-2004' then '79 - 132 MO'
        ELSE '> 133 MO' END) D
    from INDIVIDUAL_VE_TB 
    where hr_id in (select distinct hr_id from MODEL_SC_TB b where b.model_id='200')
    Group by hr_id)
group by D, hr_id

The order in which SQL statements are processed is key here:

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• DISTINCT
• UNION
• EXCEPT
• INTERSECT
• ORDER BY

Notice that GROUP BY is processed prior to SELECT. Therefore, you can't use an alias from the SELECT cause - the SQL engine will not know about it at that point. That's also why the other answers that suggested moving it into a sub-select in the FROM clause are correct. If it's done in the FROM, it will be available by the time you get to the GROUP BY.

You have to repeat the whole case-statement in the group. You can't use the alias for that.

If you are using SQL Server you can create a temporary named resultset using the WITH clause and group by that.

If not, you could alternatively create a View without the grouping and group that view by the relevant column.

Version 1 (copy & paste the computed column):

select 
    hr_id, 
    max(delivery_dt)as maxd,
    (CASE 
        WHEN max(delivery_dt) > '11-may-2010' then '< 6 MO'
        WHEN max(delivery_dt) > '11-may-2004' and  max(delivery_dt) < '11-may-2010'   then '7 - 78 MO'
        WHEN max(delivery_dt) > '11-nov-1999' and  max(delivery_dt) < '11-april-2004' then '79 - 132 MO'
        ELSE '> 133 MO'
    END) D
from 
    INDIVIDUAL_VE_TB 
where hr_id in (select distinct hr_id from MODEL_SC_TB b where b.model_id='200')
group by 
    hr_id,
    (CASE 
        WHEN max(delivery_dt) > '11-may-2010' then '< 6 MO'
        WHEN max(delivery_dt) > '11-may-2004' and  max(delivery_dt) < '11-may-2010'   then '7 - 78 MO'
        WHEN max(delivery_dt) > '11-nov-1999' and  max(delivery_dt) < '11-april-2004' then '79 - 132 MO'
        ELSE '> 133 MO' 
    END)

Version 2 (WITH clause):

with temporary(hr_id, maxd, D)
{
    select 
        hr_id, 
        max(delivery_dt)as maxd,
        (CASE 
            WHEN max(delivery_dt) > '11-may-2010' then '< 6 MO'
            WHEN max(delivery_dt) > '11-may-2004' and  max(delivery_dt) < '11-may-2010'   then '7 - 78 MO'
            WHEN max(delivery_dt) > '11-nov-1999' and  max(delivery_dt) < '11-april-2004' then '79 - 132 MO'
            ELSE '> 133 MO'
        END) D
    from 
        INDIVIDUAL_VE_TB 
    where hr_id in (select distinct hr_id from MODEL_SC_TB b where b.model_id='200')
    group by 
        hr_id
}
select 
    hr_id,
    MAX(maxd) maxd,
    D
from 
    temporary
group by
    hr_id, D

Version 3 is just version 2 in two parts.

Move it to a join so you have a groupable expression.

select hr_id, max(delivery_dt)as maxd, i.D
from 
  INDIVIDUAL_VE_TB IV1
  join (
    select
      CASE 
        IV2._YOUR_PK_HERE_ PK
        WHEN max(delivery_dt) > '11-may-2010' then '< 6 MO'
        WHEN max(delivery_dt) > '11-may-2004' and  max(delivery_dt) < '11-may-2010'   then '7 - 78 MO'
        WHEN max(delivery_dt) > '11-nov-1999' and  max(delivery_dt) < '11-april-2004' then '79 - 132 MO'
        ELSE '> 133 MO' as D
      END
    from
      INVDIVIDUAL_VE_TB IV2
  ) i on i.PK=IV1.PK

where hr_id in (select distinct hr_id from MODEL_SC_TB b where b.model_id='200')
Group by hr_id, I.D

avg, sum, max and count Functions are not allowed in the group by clause, irrespective of what other functions they are within (in this case CASE).

Try listing fields in GROUP BY you are using in CASE condition after WHEN, in this case - delivery_dt.