Hi there,

I have found a small but annoying bug. I have created a view. The
SQL-statement in the view contains a GROUP BY statement. Then I compose a
SQL-statement using this view and another GROUP BY statement and a COUNT(*)
statement. The count(*) statement doesn't count the amount of grouped
record's of the view, but it count's the amount of grouped records of the
GROUP BY in the view and of the GROUP BY in the select statement. It counts
all the records grouped instead of only the records grouped from the view.
This is wrong (IMHO). When I use a temporary table instead of a view all
things work OK. IMHO views shouldn't differ from temporary tables.

To make things a bit more clear I have add an SQL-attachment. You can run
the attachment in an empty database form psql to have a look at the problem.

I sometimes get another <<database.sql>>  error too while executing these
group statements: 'My bit's blew right of the end of the world'. (This is
when i am using an ODBC link to my db.)

Mighty thanks in advance,

Jeroen Eitjes
j.eitjes<nospam>@chem.leidenuniv.nl
eitjes<nospam>@walras.nl
Thu, 29 Mar 2001 15:30:37 +0200
Jeroen Eitjes <Eitjes@WalRas.nl>
Problem with group by in conjuction with Views
Jeroen Eitjes <Eitjes@WalRas.nl> writes:
> I have found a small but annoying bug. I have created a view. The
> SQL-statement in the view contains a GROUP BY statement. Then I compose a
> SQL-statement using this view and another GROUP BY statement and a COUNT(*)
> statement.

This sort of thing will not work at all in Postgres releases earlier
than 7.1.  Sorry.

			regards, tom lane

Thu, 29 Mar 2001 11:13:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problem with group by in conjuction with Views 