Re: [SQL] Expression with aggregate - Mailing list pgsql-sql

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