Thread: 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@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
> > 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. I think it is view? because it could be a view, or a rule? > > > > 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 > > > ************ > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
<tt>In PostgreSQL a view is a table with a special rule named: "_RETnametable" where nametable stands for the table
nameused by the view.</tt><tt></tt><p><tt>eg:</tt><br /><tt>CREATE RULE "_RETvista" AS ON SELECT TO "vista" DO INSTEAD
SELECT"a" FROM "tabella";</tt><tt></tt><p><tt>Actually, psql checks in the field pg_class.relkind to see if it is a
tableor other kind of class ('i'=index, S=sequence, 'r'=table/view, etc).</tt><br /><tt>If psql finds a 'r' into
pg_class.relkindthen it checks in the boolean field pg_class.relhasrules and prints "table" if its value is FALSE or
"view?"if its value is TRUE.</tt><br /><tt>The field pg_class.relhasrules is also used for every kind of rule in the
table,</tt><br/><tt>this is the reason why psql prints "view?" because it doesn't know for sure if the rule is</tt><br
/><tt>a"_RET..." (view) or other kind of rule.</tt><br /><tt></tt> <tt></tt><p><tt>QUERY: SELECT usename, relname,
relkind,relhasrules FROM pg_class, pg_user WHER</tt><br /><tt>E usesysid = relowner and ( relkind = 'r' OR relkind =
'i'OR relkind = 'S') and</tt><br /><tt> relname !~ '^pg_' and (relkind != 'i' OR relname !~ '^xinx') ORDER BY
relname</tt><tt></tt><p><tt>Database = prova</tt><br
/><tt> +------------------+----------------------------------+----------+</tt><br/><tt> | Owner
| Relation | Type |</tt><br
/><tt> +------------------+----------------------------------+----------+</tt><br/><tt> | postgres |
serial | sequence |</tt><br /><tt> | postgres | t_a_seq |
sequence|</tt><br /><tt> | postgres | tabella | table |</tt><br /><tt> |
postgres | vista | view? |</tt><br
/><tt> +------------------+----------------------------------+----------+</tt><tt></tt><p><tt>There'san exactly way to
knowif the table is a view or a table; the function pg_get_viewdef()</tt><br /><tt>But I don't know why psql doesn't
useit...</tt><br /><tt></tt> <tt></tt><p><tt>prova=> select pg_get_viewdef('vista');</tt><br
/><tt>pg_get_viewdef</tt><br/><tt>--------------------------</tt><br /><tt>SELECT "a" FROM "tabella";</tt><br /><tt>(1
row)</tt><br/><tt></tt> <tt></tt><p><tt>prova=> select pg_get_viewdef('tabella');</tt><br
/><tt>pg_get_viewdef</tt><br/><tt>--------------</tt><br /><tt>Not a view</tt><br /><tt>(1 row)</tt><br /><tt></tt>
<br/><tt></tt> <tt></tt><p><tt>José</tt><br /><tt></tt> <br /> <p>Bruce Momjian ha scritto: <blockquote
type="CITE">><br />> That should provide the desired results. As for the <br />> question about the view in
the\d output, I don't know <br />> why its like that, I've wondered myself. <p>I think it is view? because it could
bea view, or a rule? <p>> > <br />> > By the way, when I use \d to list the tables, why is <br />> >
aview always <br />> > shown with a '?' like in <br />> > <br />> > asdf=> \d <br />> >
Database = asdf <br />> > <br />> > <br />>
+------------------+----------------------------------+----------+<br />> > | Owner |
Relation<br />> > | Type | <br />> > <br />> > <br />>
+------------------+----------------------------------+----------+<br />> > | albert | a <br />>
> | table | <br />> > | albert | b <br />> > | table | <br />> > |
albert | c <br />> > | view? | <br />> > <br />> > <br />>
+------------------+----------------------------------+----------+<br />> > <br />> > ? Why doesn't it just
say'| albert | c | view |'? <br />> > <br />> > I'd really appreciate any hints with this, even <br />>
>though I know how to <br />> > do it (as demonstrated above) with the use of the <br />> > view. <br
/>>> <br />> > Thanks in advance for your help, <br />> > <br />> > Albert. <br />> > <br
/>>> -- <br />> > <br />> > <br />>
---------------------------------------------------------------------------<br />> > Post an / Mail to / Skribu
al:Albert Reiner <br />> > <areiner@tph.tuwien.ac.at> <br />> > <br />>
---------------------------------------------------------------------------<br />> > <br />> > ************
<br/>> > <br />> > <br />> <br />> __________________________________________________ <br />> Do
YouYahoo!? <br />> Bid and sell for free at <a href="http://auctions.yahoo.com">http://auctions.yahoo.com</a><br
/>><br />> <br />> ************ <br />> <br />> <p>-- <br /> Bruce Momjian | <a
href="http://www.op.net/~candle">http://www.op.net/~candle</a><br/> maillist@candle.pha.pa.us | (610)
853-3000<br /> + If your life is a hard drive, | 830 Blythe Avenue <br /> + Christ can be your backup.
| Drexel Hill, Pennsylvania 19026 <p>************</blockquote>