Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: HELP for group by

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@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           0
Received on Fri May 31 2002 - 11:20:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US