Thread: Problem with group by in conjuction with Views

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

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

This seems to work for me.  I used the snapshot from 3/28 on Solaris 8

SELECT service, count(*) AS GebruikersAantal
FROM tbtrouble GROUP BY service; service  | gebruikersaantal 
-----------+------------------Service 1 |                2Service 3 |                2Service 4 |                1
(3 rows)

SELECT service, count(*) AS GebruikersAantal
FROM vwtrouble GROUP BY service; service  | gebruikersaantal 
-----------+------------------Service 1 |                2Service 3 |                2Service 4 |                1
(3 rows)



> This message is in MIME format. Since your mail reader does not
understand
> this format, some or all of this message may not be legible.
> 
> 
> 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
> 
>