Bruno Wolff III <bruno@xxxxxxxx> writes:
Jon Lapham <lapham@xxxxxxxxx> wrote:
When using queries with aggregate functions, is there any way to not have to have to explicitly write all the columns names after the GROUP BY ? I would like to use a wildcard "*".
Don't those tables have primary keys? Grouping by the primay key of each table will produce the same result set as grouping by all of the columns.
Bruno, this is true, but I want all the columns to appear in the output.
Unfortunately, PG will still make him GROUP BY everything he wants to use as a non-aggregated output column. This behavior is per SQL92 spec. SQL99 added some verbiage to the effect that you only need to GROUP BY columns that the rest are functionally dependent on (this covers primary keys and some other cases); but we haven't got round to implementing that extension.
Ugh.
Since I do not want to have to re-write all my aggregate function containing queries upon modifications to the table definitions (and I do not want to write multi-thousand character long SELECT statements), maybe it is easier to use a temp table intermediary?
SELECT a.id AS aid, SUM(d.blah) AS sum_blah INTO TEMPORARY TABLE foo FROM a, b, c, d WHERE <some join conditions linking a,b,c,d>
followed by
SELECT * FROM a, b, c, foo WHERE <some join conditions linking a,b,c> AND foo.aid=a.id
Ugly... ugly... any other ideas on how to do this? My table definitions LITERALLY have hundreds of columns, and I need access to them all.
-- -**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*--- Jon Lapham <lapham@xxxxxxxxx> Rio de Janeiro, Brasil Personal: http://www.jandr.org/ ***-*--*----*-------*------------*--------------------*---------------
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
