Re: HELP for group by
From: Mikito Harakiri <mikharakiri_at_yahoo.com>
Date: 31 May 2002 09:20:58 -0700
Message-ID: <bdf69bdf.0205310820.30a7c7bf_at_posting.google.com>
) group by job
Date: 31 May 2002 09:20:58 -0700
Message-ID: <bdf69bdf.0205310820.30a7c7bf_at_posting.google.com>
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
) group by job
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) group by job
JOB SUM(C)
--------- ----------
ANALYST 2 CLERK 0 MANAGER 2 PRESIDENT 1 SALESMAN 0Received on Fri May 31 2002 - 18:20:58 CEST