CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005count
Robert Dinaro
Robert Dinaro

Reputation: 538

Count rows without issuing a separate count

I currently have two separate queries:

1) returns the actual resultset based on various filters.

select a, b, c from TableA
where x = 123
and y = 'ABC'
and z = 999

2) displays the total number of rows.

select count(*) from TableA
where x = 123
and y = 'ABC'
and z = 999

So in short I am running same query twice. The queries I have put above as examples are much simpler than what I have with multiple joins and many filters.

Is there a better way of achieving the same?

Upvotes: 2

Views: 83

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

select a, b, c, count(*) over() as total
from dbo.TableA
where x = 123
and y = 'ABC'
and z = 999;

Upvotes: 4

dougajmcdonald
dougajmcdonald

Reputation: 20037

Couldn't you just return the count as a column in your first query?

e.g.

select a, b, c, count(*) as total from TableA
where x = 123
and y = 'ABC'
and z = 999

This would avoid running it twice

Upvotes: 0

Related Questions