sql query to get results specific rows but count of all the rows of table

Question

I am trying to create a query to get specific rows on conditions and along with that, all the rows present in the table. I am using the following query for this

select name, email, count(users.id) OVER (PARTITION BY users.id) from users where user.city='new york';

with the count(), I want to get the count of all the rows present irrespective of the where clause.

I am trying to reduce the query because right now in my list page view I am doing two queries one for count of all the records and other for the filtered list of records. But I want to do that in the single query.

Answer 1

with the count(), I want to get the count of all the rows present irrespective of the where clause.

This can be done with an independent subquery in the select-list, if it returns a single value, as in:

select name, email, (select count(*) from users) as "Total" from users WHERE ...

Example:

postgres=> select relname,(select count(*) from pg_class) as "Total"
           from  pg_class where relname like 'a%';

              relname              | Total 
-----------------------------------+-------
 administrable_role_authorizations |   348
 applicable_roles                  |   348
 attributes                        |   348

Note that from the point of view of the relational model, this is an unorthodox trick . Normally each query is an answer to one question with a structure of result that fits exactly that question. Doing two queries would be right here.