Postgres Pro
Downloads
Home   >   mailing lists

Thread: counting rows of grouped query (problem with views)

counting rows of grouped query (problem with views)

From
Nabil Sayegh
Date:
Hi all,

(short)
is there any possibility to count the number of rows resulting from a
grouped query (from a view containing a grouped query) ?

(long)
CREATE TABLE tmp (a int, b int, c text);
INSERT INTO tmp values (1, 1, 'a');
INSERT INTO tmp values (2, 1, 'b');
INSERT INTO tmp values (1, 2, 'c');
INSERT INTO tmp values (2, 2, 'd');

CREATE view damn as select a, b from tmp group by a, b;

I have no rights to access tmp directly, I have to use the view !!!

praxis=# select * from damn where b=2;
a | b
---+---
1 | 2
2 | 2
(2 rows)

okay, this is what I want.

praxis=# select count(*) from damn where b=2;
count
-------
     1
     1
(2 rows)

I dont want the aggregate function to be used on each group, I want it
to count all the rows.
IMHO the view should act as a table (and therefor should UNGROUP the
result)

P.S.: I use 7.0.3
P.P.S.: How stable is 7.1

TIA
cu
--

Nabil Sayegh
GPG-Key available at http://www.sayegh.de
(see http://www.gnupg.org for details)

Re: counting rows of grouped query (problem with views)

From
Nabil Sayegh
Date:
On 09 Mar 2001 19:08:12 +0100, Nabil Sayegh wrote:
> Hi all,
>
> (short)
> is there any possibility to count the number of rows resulting from a
> grouped query (from a view containing a grouped query) ?

[...]

> I dont want the aggregate function to be used on each group, I want it
> to count all the rows.
> IMHO the view should act as a table (and therefor should UNGROUP the
> result)
>
> P.S.: I use 7.0.3


Nobody ? Come on.
Am I the only one who finds this behaviour of VIEWs strange ?

TIA
cu

--
 Nabil Sayegh
 GPG-Key available at http://www.sayegh.de
 (see http://www.gnupg.org for details)

Re: counting rows of grouped query (problem with views)

From
Tom Lane
Date:
Nabil Sayegh <nsmail@sayegh.de> writes:
>> is there any possibility to count the number of rows resulting from a
>> grouped query (from a view containing a grouped query) ?

Grouped views don't work very well in versions before 7.1.

            regards, tom lane

Re: counting rows of grouped query (problem with views)

From
Nabil Sayegh
Date:
On 09 Mar 2001 19:08:12 +0100, Nabil Sayegh wrote:
> Hi all,
>
> (short)
> is there any possibility to count the number of rows resulting from a
> grouped query (from a view containing a grouped query) ?

[...]

> I dont want the aggregate function to be used on each group, I want it
> to count all the rows.
> IMHO the view should act as a table (and therefor should UNGROUP the
> result)


Nobody ? Come on.
Am I the only one who finds this behaviour of VIEWs strange ?

TIA
cu

--
 Nabil Sayegh
 GPG-Key available at http://www.sayegh.de
 (see http://www.gnupg.org for details)