Row Count

[Sergey Gigoyan]

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'

[Thomas Kellerer]

Post by Sergey Gigoyan
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';

[Andreas Kretschmer]

Post by Sergey Gigoyan
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

[Thomas Kellerer]

Post by Andreas Kretschmer
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.

[Sergey Konoplev]

On Mon, Feb 11, 2013 at 11:05 PM, Sergey Gigoyan

Post by Sergey Gigoyan
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(...);

Post by Sergey Gigoyan
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 [phone]
Russia, Moscow [phone]
Russia, Krasnodar [phone]

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