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@flex.ro
        george@cicnet.ro
        Braila, ROMANIA.