Re: HELP for group by

From: Nis Jorgensen <nis_at_dkik.dk>
Date: Fri, 31 May 2002 10:58:04 +0200
Message-ID: <rdeefu85pn2s5ct2h07g46kqqncb4ecl1k_at_4ax.com>


On Fri, 31 May 2002 09:29:29 +0200, Ralf Mrowka <ralf.mrowka_at_charite.de> wrote:

>I have the following table
>
>member1 member2 value
>A B 10
>A C 11
>A L 9
>A F 2
>B K 10
>B O 11
>
>
>I want to count how often each member1 meets any member2 given that
>value is
>larger that XX
>this works fine with
>
>select member1, count(member2) from table where value<XX group by
>member1 ;
>
>The problem is that I also want to see in the result member1 if member1
>e.g. A
>meets none (0) member2 when XX is e.g. 40.

select member1, SUM(CASE WHEN value<XX THEN 1 ELSE 0) from table group by member1 ;

(not sure about the CASE syntax)

-- 
Nis Jorgensen
Amsterdam

Please include only relevant quotes, and reply below the quoted text. Thanks
Received on Fri May 31 2002 - 10:58:04 CEST

Original text of this message