pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Barron <kain224@yahoo.com>
To: Albert REINER <areiner@tph.tuwien.ac.at>
To: PostgreSQL-SQL <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] Expression with aggregate
Date: Mon, 23 Aug 1999 15:28:23 -0700 (PDT)
Message-ID: <19990823222823.28535.rocketmail@web804.mail.yahoo.com> (raw)

Hi Albert,

Try this one:

SELECT a.id, a.name, a.d-sum(b.c) AS diff FROM a, b
WHERE a.id=1 AND a.id = b.a_id GROUP BY a.id, a.name,
a.d;

That should provide the desired results.  As for the
question about the view in the \d output, I don't know
why its like that, I've wondered myself.

Cheers,
Scott

--- Albert REINER <areiner@tph.tuwien.ac.at> wrote:
> Hi!
> 
> This is actually a follow-up question on my posting
> "[NOVICE] Join
> with aggregate" a couple of days ago; thanks again
> to all of you who
> tried to help me, and actually did help me a lot.
> This time, my
> problem is in using in a join the aggregate result
> in conjunction with
> a non-aggregate field in a mathematical expression.
> More clearly (and
> again I am stripping down the example to the
> simplest form; actually I
> am thinking of recordings on video tapes, and I also
> take into account
> the distinction between SP/LP recordings in the
> summation over playing
> times):
> 
> I'm using Postgres 6.5.1, and I have tables like the
> following:
> 
> asdf=> create table a (id int4, name text, d int2);
> CREATE
> asdf=> create table b (a_id int4, c int2);
> CREATE
> 
> with sample data:
> 
> asdf=> insert into a values (1, 'Number one', 800);
> INSERT 418805 1
> asdf=> insert into b values (1, 100);
> INSERT 418806 1
> asdf=> insert into b values (1, 200);
> INSERT 418807 1
> 
> From this I want to produce a table having
> 
> id | name       |diff
> ---+------------+----
> 1  | Number one | 500
> 
> , where diff = 800 - sum(100, 200) = 500. I know
> that I can achieve
> this with a temporary table, or with a view, using:
> 
> asdf=> create view c as select id, name, d, sum(b.c)
> from a, b where
> id = a_id group by id, name, d;
> CREATE
> asdf=> select id, name, d-sum as diff from c;
> id|name      |diff
> --+----------+----
>  1|Number one| 500
> (1 row)
> 
> But what I really want to do is something like:
> 
> asdf=> select id, name, d-sum(b.c) as diff from a, b
> where id = a_id
> group by id, name, diff;
> ERROR:  Aggregates not allowed in GROUP BY clause
> 
> or:
> 
> asdf=> select id, name, d-sum(b.c) as diff from a, b
> where id = a_id
> group by id, name;
> ERROR:  Illegal use of aggregates or non-group
> column in target list
> 
> Is there a way to do this without the detour via the
> view? Or are
> there views just for this reason?
> 
> By the way, when I use \d to list the tables, why is
> a view always
> shown with a '?' like in
> 
> asdf=> \d
> Database    = asdf
> 
>
+------------------+----------------------------------+----------+
>  |  Owner           |             Relation          
>   |   Type   |
> 
>
+------------------+----------------------------------+----------+
>  | albert           | a                             
>   | table    |
>  | albert           | b                             
>   | table    |
>  | albert           | c                             
>   | view?    |
> 
>
+------------------+----------------------------------+----------+
> 
> ? Why doesn't it just say '| albert | c | view |'?
> 
> I'd really appreciate any hints with this, even
> though I know how to
> do it (as demonstrated above) with the use of the
> view.
> 
> Thanks in advance for your help,
> 
> Albert.
> 
> -- 
> 
>
---------------------------------------------------------------------------
>   Post an / Mail to / Skribu al: Albert Reiner
> <areiner@tph.tuwien.ac.at>
>
---------------------------------------------------------------------------
> 
> ************
> 
> 

__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com




view thread (4+ messages)  latest in thread

Message-ID: <19990823222823.28535.rocketmail@web804.mail.yahoo.com>
Permalink:  ../19990823222823.28535.rocketmail@web804.mail.yahoo.com/
Also on:    postgresql.org/message-id/19990823222823.28535.rocketmail@web804.mail.yahoo.com

 · 

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: kain224@yahoo.com, areiner@tph.tuwien.ac.at
  Subject: Re: [SQL] Expression with aggregate
  In-Reply-To: <19990823222823.28535.rocketmail@web804.mail.yahoo.com>
  References: <19990823222823.28535.rocketmail@web804.mail.yahoo.com>

* 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