From: greg@turnstep.com
To: pgsql-sql@postgresql.org
Cc: cgg007@yahoo.com
Cc: cgg007@yahoo.com
Subject: Re: Elegant SQL solution:
In-Reply-To: <20030606162635.97132.qmail@web13807.mail.yahoo.com>
Date: Sun,  8 Jun 2003 01:58:32 -0000
Message-ID: <152a2a7e34c78ff65406189821f83fa5@biglumber.com>


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> SELECT date_part('month',rowdate) as month, count(*) as rows FROM mytable GROUP
> BY month;
>...
> I could create a one-column table with values 1 - 12 in it, and select from
> that table with a where clause matching "month". I could also create a view 
> "SELECT 1 UNION SELECT 2 UNION ..." and select against the view. There MUST be
> a more elegant way to do this.

The first solution is probably the best one. It does not seem that "unelegant" 
to me. Another way would be just to do it in the application itself.

...or you could consider this one I came up with. Use at your own risk ;)

SELECT dos.mym AS "Month", COALESCE(uno.rc,0) AS "Total" FROM 
  (SELECT date_part('month',rowdate) AS mym, count(*) AS rc
   FROM mytable GROUP BY 1) AS uno
RIGHT JOIN 
  (SELECT oid::integer-15 AS mym 
   FROM pg_type ORDER BY oid ASC LIMIT 12) AS dos
USING (mym);

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200306072131

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+4phlvJuQZxSWSsgRAqLRAJsGr5YNiGXKoXBOWq6+3OpSZXOG3ACdFr2F
ywb1tBYllZt6CKtKYhoc7G4=
=6yvp
-----END PGP SIGNATURE-----