FAQ
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)

Search Discussions

  • Nabil Sayegh at Mar 12, 2001 at 10:38 pm

    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)
  • Tom Lane at Mar 12, 2001 at 11:45 pm

    Nabil Sayegh 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
  • Nabil Sayegh at Mar 13, 2001 at 1:47 am
    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)
  • Nabil Sayegh at Mar 13, 2001 at 2:37 pm

    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)

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMar 9, '01 at 6:03p
activeMar 13, '01 at 2:37p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase