This is a very NEWBIE suggestion. I am fully prepared to be laughed out of
town...
But the where clause defines the result of the aggregate function (in this
case the SUM)?
Is the only reason for needing the GROUP BY CLAUSE is because the aggregate
function demands it?
If so could something like the following work where we pass the where clause
conditions into the function and it performs the aggregate function and
returns.. I am guessing this would be extremely inefficient?
select A.*,B.*,C.*,my_cheating_sum(a.id,b.id,c.id) from a,b,c,
where some conditions;
-- Warning complete gibberish pseudo code now follows
function my_cheating_sum(a.id,b.id,c.id)
select SUM(xxx) from a,b,c where some conditions;
end function;
----- Original Message -----
From: "Bruno Wolff III" <bruno@wolff.to>
To: "Jon Lapham" <lapham@jandr.org>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; <pgsql-general@postgresql.org>
Sent: Saturday, February 19, 2005 13:36
Subject: Re: [GENERAL] Group By and wildcards...
> On Sat, Feb 19, 2005 at 15:59:52 -0200,
> Jon Lapham <lapham@jandr.org> wrote:
> >
> > 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.
>
> Well if you are thinking about the above than you might be interested in
> seeing a more sketched out example of what I was suggesting in my
> followup after Tom's correction.
>
> SELECT a.*, b.*, c.*, e.d1
> FROM a, b, c,
> (SELECT a.id AS a1, b.id AS b1 , c.id AS c1, sum(d) AS d1
> FROM a, b, c, d
> WHERE <some join conditions linking a,b,c,d>
> GROUP BY a1, b1, c1) AS e
> WHERE
> a.id = e.a1 AND
> b.id = e.b1 AND
> c.id = e.c1
> ;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
