Re: [SQL] joins with aggregates

Remigiusz Soko³owski wrote:

> Hi!
> I'm wonder if it is possible to make a query which joins data from two
> tables and also aggregates some columns of one of them
> I mean something like
> SELECT p1.id_prod, p1.name_prod, d1.id_prod, sum(d1.quant_detal) FROM
> prod p1, detal d1 WHERE p1.id_prod=d1.id_prod
> GROUP BY p1.id_prod;
> TIA
> Rem

Try:

Create a new file (named ex: test.pl) with:

CREATE FUNCTION "calc_sum" ("int4") RETURNS "float8" AS '

declare
var float8;

begin
select sum(quant_detal) into var from detal where id_prod = $1;
return var;
end;

' LANGUAGE 'plpgsql';

or use the file who came with this mail.

Load the file in psql: \i test.pl and...:

SELECT p1.id_prod, p1.name_prod, d1.id_prod, calc_sum(p1.id_prod)
FROM prod p1, detal d1
WHERE p1.id_prod=d1.id_prod
GROUP BY p1.id_prod;

I create the function in PostgreSQL 6.4 on Red Hat Linux 5.1.
I don't know how this work on other versions.

I use id_prod as int4 and quant_detal as float8.

Best,
George Moga,
george(at)flex(dot)ro
george(at)cicnet(dot)ro
Braila, ROMANIA.