X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 369AC47631F
	for <pgsql-sql@postgresql.org>; Fri,  4 Apr 2003 11:16:20 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id 7A3B147631B
	for <pgsql-sql@postgresql.org>; Fri,  4 Apr 2003 11:16:19 -0500 (EST)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2942345; Fri, 04 Apr 2003 08:16:16 -0800
Content-Type: text/plain;
  charset="iso-8859-2"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: "Tomasz Myrta" <jasiek@klaster.net>,
	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:16:01 -0800
User-Agent: KMail/1.4.3
References: <1049403724.13799.5473.camel@camel>
	<20030404080209.M3259@klaster.net>
In-Reply-To: <20030404080209.M3259@klaster.net>
MIME-Version: 1.0
Message-Id: <200304040816.01369.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable
X-Spam-Status: No, hits=-22.7 required=5.0
	tests=BAYES_01,IN_REP_TO,REFERENCES,USER_AGENT_KMAIL
	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/67
X-Sequence-Number: 12768

Tomasz,

> What about this:
> select
>   event,
>   sum(case when level=3D1 then 1 else 0 end) as ones,
>   sum(case when level=3D2 then 1 else 0 end) as twos,
>   sum(case when level=3D3 then 1 else 0 end) as threes
> from baz
> group by event;

That version is only more efficient for small data sets.    I've generally=
=20
found that case statements are slower than subselects for large data sets.=
=20
YMMV.

BTW, while it won't be faster, Joe Conway's crosstab function in /tablefunc=
=20
does this kind of transformation.

--=20
Josh Berkus
Aglio Database Solutions
San Francisco