X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 2C32D474E44
	for <pgsql-sql@postgresql.org>; Fri,  4 Apr 2003 01:02:12 -0500 (EST)
Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id 8C78F474E42
	for <pgsql-sql@postgresql.org>; Fri,  4 Apr 2003 01:02:10 -0500 (EST)
Received: from lamer.pl (localhost [127.0.0.1])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 2DEF16A283; Fri,  4 Apr 2003 08:02:09 +0200 (CEST)
From: "Tomasz Myrta" <jasiek@klaster.net>
To: Robert Treat <xzilla@users.sourceforge.net>,
	pgsql-sql@postgresql.org
Subject: Re: can i make this sql query more efficiant?
Date: Fri, 4 Apr 2003 08:02:09 +0900
Message-Id: <20030404080209.M3259@klaster.net>
In-Reply-To: <1049403724.13799.5473.camel@camel>
References: <1049403724.13799.5473.camel@camel>
X-Mailer: Open WebMail 1.62 20020221
X-OriginatingIP: 217.96.97.171 (jasiek)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-2
X-Spam-Status: No, hits=-16.2 required=5.0
	tests=BAYES_01,DATE_IN_PAST_06_12,IN_REP_TO,REFERENCES
	autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/62
X-Sequence-Number: 12763

<cut>
> select 
> 	event, 
> 	(select count(*) from baz a 
> 		where level = 1 and a.event=baz.event) as ones, 
> 	(select count(*) from baz a 
> 		where level = 2 and a.event=baz.event) as twos, 
> 	(select count(*) from baz a 
> 		where level = 3 and a.event=baz.event) as threes
> from
> 	 baz
> group by 
> 	event;
> 
> which gives me:
> 
>  event | ones | twos | threes 
> -------+------+------+--------
>  x     |    1 |    1 |      1
>  y     |    0 |    1 |      2
> (2 rows)
<cut>
What about this:
select
  event,
  sum(case when level=1 then 1 else 0 end) as ones,
  sum(case when level=2 then 1 else 0 end) as twos,
  sum(case when level=3 then 1 else 0 end) as threes
from baz
group by event;

Regards,
Tomasz Myrta