Обсуждение: Row Count

How can I get selected row count in one statement?
For example, I can do that by two select statements

select id, name, addrress, (select count(*) from(select id
                                                                     from users
                                                                      where name<>'examplename')
from users
where name<>'examplename'

or

declare rowcount integer
...............
rowcount:=select count(*) from(select id
                                               from users
                                                where name<>'examplename') as rst

select id, name, address, rowcount as rowcount
from users
where name<>'examplename'

Sergey Gigoyan, 12.02.2013 08:05:
> How can I get selected row count in one statement?
> For example, I can do that by two select statements
>
> select id, name, addrress, (select count(*) from(select id
>                                                                       from users
>                                                                        where name<>'examplename')
> from users
> where name<>'examplename'
>

select id,
        name,
        addrress,
        count(*) over () as row_count
from users
where name <> 'examplename';

Sergey Gigoyan <sergey.gigoyan@gmail.com> hat am 12. Februar 2013 um 08:05
geschrieben:
> How can I get selected row count in one statement?
> For example, I can do that by two select statements
>
> select id, name, addrress, (select count(*) from(select id


select id, name, ..., row_number() over () from ...

works since 8.4.


Andreas

On Mon, Feb 11, 2013 at 11:05 PM, Sergey Gigoyan
<sergey.gigoyan@gmail.com> wrote:
> How can I get selected row count in one statement?

Usually it is immediately available after statement execution without
additional query.

For example in PL/PgSQL you can use GET DIAGNOSTICS straight after your SELECT:

SELECT INTO _id, _name, _address id, name, address
FROM users WHERE name <> 'examplename';

GET DIAGNOSTICS _row_count = ROW_COUNT;

In external languages like Perl:

$sth = $dbh->prepare(
    "select id, name, address, rowcount as rowcount".
    "from users where name<>'examplename'");

$row_count = $sth->execute(...);

> For example, I can do that by two select statements
>
> select id, name, addrress, (select count(*) from(select id
>                                                                      from
> users
>                                                                       where
> name<>'examplename')
> from users
> where name<>'examplename'
>
> or
>
> declare rowcount integer
> ...............
> rowcount:=select count(*) from(select id
>                                                from users
>                                                 where name<>'examplename')
> as rst
>
> select id, name, address, rowcount as rowcount
> from users
> where name<>'examplename'
>



--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979

Skype: gray-hemp
Jabber: gray.ru@gmail.com

Andreas Kretschmer, 12.02.2013 09:03:
> select id, name, ..., row_number() over () from ...
>
> works since 8.4.
>

That will only return the row count in the *last* row, not in all of them.