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