Received: from rhea.arexim.ro (root@mail.arexim.ro [193.230.225.5]) by hub.org (8.9.1/8.9.1) with ESMTP id KAA29390 for ; Fri, 11 Dec 1998 10:58:20 -0500 (EST) (envelope-from george@flex.ro) Received: from arexim.arexim.ro (root@arexim.arexim.ro [193.230.225.2] (may be forged)) by rhea.arexim.ro (8.9.0/8.9.0) with ESMTP id SAA21913 for ; Fri, 11 Dec 1998 18:56:15 +0200 Received: from flex.ro ([193.230.255.93]) by arexim.arexim.ro (8.7.5/8.7.3) with ESMTP id RAA03573 for ; Fri, 11 Dec 1998 17:57:22 +0200 Sender: gestoc@arexim.ro Message-ID: <3671408F.7DC2011@flex.ro> Date: Fri, 11 Dec 1998 17:55:59 +0200 From: George Moga X-Mailer: Mozilla 4.05 [en] (X11; I; Linux 2.0.34 i586) MIME-Version: 1.0 To: SQL PostgreSQL Subject: Re: [SQL] joins with aggregates References: <36712F3D.8DC8D68F@gdansk.sprint.pl> Content-Type: multipart/mixed; boundary="------------2EEB74F1D1A12AF8145484B0" This is a multi-part message in MIME format. --------------2EEB74F1D1A12AF8145484B0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --------------2EEB74F1D1A12AF8145484B0 Content-Type: application/x-perl; name="test.pl" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="test.pl" 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'; --------------2EEB74F1D1A12AF8145484B0--