Re: HELP for group by

Nis Jorgensen <nis_at_dkik.dk> wrote in message news:<rdeefu85pn2s5ct2h07g46kqqncb4ecl1k_at_4ax.com>...
> select member1, SUM(CASE WHEN value<XX THEN 1 ELSE 0) from table group
> by member1 ;

Case is a beatuful solution, of course. Other variations:

sql>select job, max(c) from (

     select job, count(1) c from emp where sal > 2500 group by job
     union all
     select job, 0 c from emp 

JOB MAX(C)
--------- ----------

ANALYST            2
CLERK              0
MANAGER            2
PRESIDENT          1
SALESMAN           0


sql>select job, sum(c) from (
     select job, 1 c from emp where sal > 2500
     union all
     select job, 0 c from emp where sal <= 2500 

JOB SUM(C)
--------- ----------

ANALYST            2
CLERK              0
MANAGER            2
PRESIDENT          1
SALESMAN           0