Received: from mps2.plala.or.jp (mpsb-nat02.plala.or.jp [202.212.114.145]) by postgresql.org (Postfix) with ESMTP id 5175D479094 for ; Mon, 18 Mar 2002 10:56:34 -0500 (EST) Received: from vip333 ([218.47.225.157]) by mps2.plala.or.jp with ESMTP id <20020318155637.YBOZ11389.mps2.plala.or.jp@vip333>; Tue, 19 Mar 2002 00:56:37 +0900 Date: Tue, 19 Mar 2002 00:58:25 +0900 From: Masaru Sugawara To: "Dorian Taylor" Subject: Re: Creative use of CASE in a GROUP BY clause Cc: pgsql-sql@postgresql.org In-Reply-To: References: Errors-To: rk73@sea.plala.or.jp Message-Id: <20020319004233.2FBF.RK73@sea.plala.or.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Becky! ver. 2.00.08 X-Archive-Number: 200203/224 X-Sequence-Number: 7103 On Mon, 18 Mar 2002 13:14:46 "Dorian Taylor" wrote: > I'm trying to figure out how to craft a query that I can embed into a piece > of code that can be passed a bind value in order to dictate how it should > behave on GROUP BY, sparing me from having to write separate queries for N > functions and/or columns I may want to group my query by. > > e.g. SELECT COUNT(foo) FROM bar GROUP BY CASE (? IS NOT NULL) THEN thiscol > ELSE thatcol END > > If thiscol and thatcol aren't the same type, the CASE bombs out. I suppose If so, you may need to cast one of them. e.g. SELECT COUNT(foo) FROM bar GROUP BY CASE WHEN (? IS NOT NULL) THEN thiscol::TEXT ELSE thatcol -- if the type of thatcol is TEXT END; > ultimately it would be a matter of reducing each column definition in the > CASE to a boolean expression, but I'm not sure what that would syntactically > look like, as simply declaring a column in GROUP BY is obviously shorthand > for something. > Regards, Masaru Sugawara