pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

Message-ID: <3671408F.7DC2011@flex.ro>
Permalink:  ../3671408F.7DC2011@flex.ro/
Also on:    postgresql.org/message-id/3671408F.7DC2011@flex.ro

 · 

reply

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