pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: Dorian Taylor <leg0@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Creative use of CASE in a GROUP BY clause
Date: Tue, 19 Mar 2002 00:58:25 +0900
Message-ID: <20020319004233.2FBF.RK73@sea.plala.or.jp> (raw)
In-Reply-To: <F4zhQoKH6tw5u3A64Bo00002071@hotmail.com>
References: <F4zhQoKH6tw5u3A64Bo00002071@hotmail.com>

On Mon, 18 Mar 2002 13:14:46 
"Dorian Taylor" <leg0@hotmail.com> 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





view thread (2+ messages)

Message-ID: <20020319004233.2FBF.RK73@sea.plala.or.jp>
Permalink:  ../20020319004233.2FBF.RK73@sea.plala.or.jp/
Also on:    postgresql.org/message-id/20020319004233.2FBF.RK73@sea.plala.or.jp

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: rk73@sea.plala.or.jp, leg0@hotmail.com
  Subject: Re: Creative use of CASE in a GROUP BY clause
  In-Reply-To: <20020319004233.2FBF.RK73@sea.plala.or.jp>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox