pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedElegant SQL solution:
4+ messages / 4 participants
[nested] [flat]
* Elegant SQL solution:
@ 2003-06-06 16:26 Chris Gamache <cgg007@yahoo.com>
0 siblings, 3 replies; 4+ messages in thread
From: Chris Gamache @ 2003-06-06 16:26 UTC (permalink / raw)
To: pgsql-sql
There are so many (bad) ways to skin this cat... I'm looking for a more elegant
solution.
If I
SELECT date_part('month',rowdate) as month, count(*) as rows FROM mytable GROUP
BY month;
It might only return
month | rows
-------+------
1 | 234
3 | 998
4 | 403
5 | 252
10 | 643
12 | 933
I would like:
month | rows
-------+------
1 | 234
2 | 0
3 | 998
4 | 403
5 | 252
6 | 0
7 | 0
8 | 0
9 | 0
10 | 643
11 | 0
12 | 933
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.
Any thoughts?
__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Elegant SQL solution:
@ 2003-06-08 01:54 Josh Berkus <josh@agliodbs.com>
parent: Chris Gamache <cgg007@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2003-06-08 01:54 UTC (permalink / raw)
To: cgg007@yahoo.com; pgsql-sql
CGG:
> I could create a one-column table with values 1 - 12 in it, and select from
> that table with a where clause matching "month".
This is probably the simplest, most elegant solution. It is also the "most
relational".
> I could also create a view
> "SELECT 1 UNION SELECT 2 UNION ..." and select against the view.
This would be both awkward and have appaling performance.
> There MUST
> be a more elegant way to do this.
Another method would be to write a set returning function that generates the
months and corresponds them to a cursor of the totals and outputs that.
However, I think your first method is likely to be the fastest and easiest to
maintain.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Elegant SQL solution:
@ 2003-06-08 01:58 greg@turnstep.com
parent: Chris Gamache <cgg007@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: greg@turnstep.com @ 2003-06-08 01:58 UTC (permalink / raw)
To: pgsql-sql; +Cc: cgg007@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-----
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Elegant SQL solution:
@ 2003-06-08 11:54 Ian Barwick <barwick@gmx.net>
parent: Chris Gamache <cgg007@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Ian Barwick @ 2003-06-08 11:54 UTC (permalink / raw)
To: cgg007@yahoo.com; pgsql-sql
On Friday 06 June 2003 18:26, Chris Gamache wrote:
> 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.
You probably need a pivot table (the one-column table with values 1 - 12).
Oracle Magazine had a useful article on this subject (relevant for none-Oracle
SQL too) a while back:
http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html
(registration probably required).
Of course you could also use a set returning function a la:
CREATE OR REPLACE FUNCTION months() RETURNS SETOF INT AS '
BEGIN
FOR i IN 1..12 LOOP
RETURN NEXT i;
END LOOP;
RETURN;
END;' LANGUAGE 'plpgsql';
Ian Barwick
barwick@gmx.net
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-06-08 11:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-06 16:26 Elegant SQL solution: Chris Gamache <cgg007@yahoo.com>
2003-06-08 01:54 ` Josh Berkus <josh@agliodbs.com>
2003-06-08 01:58 ` greg@turnstep.com
2003-06-08 11:54 ` Ian Barwick <barwick@gmx.net>
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