How to count rows?

[milton747]

I have working SQL to count number of rows returned.
When I insert a WHERE clause for VT(Vermont), the correct 4 rows are returned, but the row count is still a huge number. (I was expecting the count to be 4 to match the 4 rows returned.)

Can anyone suggest SQL to count number of rows returned AFTER the WHERE clause is applied?
Thanks. Milt.

SELECT
brnNumber AS brnNumber, brnCity AS brnCity, x.rc
FROM branchlocations,
(
SELECT
count(*) AS rc
FROM branchlocations
)
AS x
WHERE brnState = 'VT'

----------------------------------
Simply delete the WHERE line and all rows are returned. But the row count is always the same...the entire table, about 800.
-----------------------------------

 

[milton747]

SOLUTION.... Include where in count sub-query. Sorry.

SELECT
brnNumber AS brnNumber, brnCity AS brnCity, x.rc
FROM branchlocations,
(
SELECT
count(*) AS rc
FROM branchlocations
WHERE brnState = 'VT'
)
AS x
WHERE brnState = 'VT'

 

[r937]

just curious...

where are you using the results of this query?

if it's in an application language like php, you needn't burden mysql with the extra query to get the count -- just use php's mysql_num_rows function right after executing the query, and it'll tell you how many rows were returned

other application languages have similar functions

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon