Re: [SQL] Expression with aggregate

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(at)tph(dot)tuwien(dot)ac(dot)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(at)tph(dot)tuwien(dot)ac(dot)at>
>
---------------------------------------------------------------------------
>
> ************
>
>

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