Re: Case statement in Group by ?

Having clause is redundant. The query could always be rewritten not to use it.

Try eliminating having by splitting your query into 2 parts: 1. Inner query with group by
2. Outer query which is a selection operation applied to the inner query.

In other words "having" is a filter on top of grouping with aggregation.

Also, there is no such thing as a conditional "group by". Group by is an generalized projection: you should specify the columns you want to project your original relation into, together with additional aggregated expressions. You can use case operator to bulid a calculated columns, however, eg

select sum(sal) from emp
group by case when deptno = 10 then deptno else mgr end;

The point is however, if the calculated column "case when deptno = 10 then deptno else mgr end" makes any sence in the context of your query. Received on Wed Feb 09 2005 - 18:11:43 CST