From: George Moga <george@flex.ro>
To: SQL PostgreSQL <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] joins with aggregates
Date: Fri, 11 Dec 1998 17:55:59 +0200
Message-ID: <3671408F.7DC2011@flex.ro> (raw)
References: <36712F3D.8DC8D68F@gdansk.sprint.pl>
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.
Attachments:
test.pl (application/x-perl, 194B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: george@flex.ro
Subject: Re: [SQL] joins with aggregates
In-Reply-To: <3671408F.7DC2011@flex.ro>
References: <36712F3D.8DC8D68F@gdansk.sprint.pl> <3671408F.7DC2011@flex.ro>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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