[sql] How to use count and group by at the same select statement

I have an sql select query that has a group by. I want to count all the records after the group by statement. Is there a way for this directly from sql? For example, having a table with users I want to select the different towns and the total number of users

select town, count(*) from user
group by town

I want to have a column with all the towns and another with the number of users in all rows.

An example of the result for having 3 towns and 58 users in total is :

Town         Count
Copenhagen   58
NewYork      58
Athens       58

The other way is:

/* Number of rows in a derived table called d1. */
select count(*) from
(
  /* Number of times each town appears in user. */
  select town, count(*)
  from user
  group by town
) d1

With Oracle you could use analytic functions:

select town, count(town), sum(count(town)) over () total_count from user
group by town

Your other options is to use a subquery:

select town, count(town), (select count(town) from user) as total_count from user
group by town

I know this is an old post, in SQL Server:

select  isnull(town,'TOTAL') Town, count(*) cnt
from    user
group by town WITH ROLLUP

Town         cnt
Copenhagen   58
NewYork      58
Athens       58
TOTAL        174

if You Want to use Select All Query With Count Option, try this...

 select a.*, (Select count(b.name) from table_name as b where Condition) as totCount from table_name  as a where where Condition

If you want to order by count (sound simple but i can`t found an answer on stack of how to do that) you can do:

        SELECT town, count(town) as total FROM user
        GROUP BY town ORDER BY total DESC

You can use COUNT(DISTINCT ...) :

SELECT COUNT(DISTINCT town) 
FROM user

You can use DISTINCT inside the COUNT like what milkovsky said

in my case:

select COUNT(distinct user_id) from answers_votes where answer_id in (694,695);

This will pull the count of answer votes considered the same user_id as one count

Ten non-deleted answers; most do not do what the user asked for. Most Answers mis-read the question as thinking that there are 58 users in each town instead of 58 in total. Even the few that are correct are not optimal.

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

SELECT  province, total_cities
    FROM       ( SELECT  DISTINCT province  FROM  canada ) AS provinces
    CROSS JOIN ( SELECT  COUNT(*) total_cities  FROM  canada ) AS tot;
+---------------------------+--------------+
| province                  | total_cities |
+---------------------------+--------------+
| Alberta                   |         5484 |
| British Columbia          |         5484 |
| Manitoba                  |         5484 |
| New Brunswick             |         5484 |
| Newfoundland and Labrador |         5484 |
| Northwest Territories     |         5484 |
| Nova Scotia               |         5484 |
| Nunavut                   |         5484 |
| Ontario                   |         5484 |
| Prince Edward Island      |         5484 |
| Quebec                    |         5484 |
| Saskatchewan              |         5484 |
| Yukon                     |         5484 |
+---------------------------+--------------+
13 rows in set (0.01 sec)

SHOW session status LIKE 'Handler%';

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 4     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 3     |
| Handler_read_key           | 16    |
| Handler_read_last          | 1     |
| Handler_read_next          | 5484  |  -- One table scan to get COUNT(*)
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 15    |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 14    |  -- leapfrog through index to find provinces  
+----------------------------+-------+

In the OP's context:

SELECT  town, total_users
    FROM       ( SELECT  DISTINCT town  FROM  canada ) AS towns
    CROSS JOIN ( SELECT  COUNT(*) total_users  FROM  canada ) AS tot;

Since there is only one row from tot, the CROSS JOIN is not as voluminous as it might otherwise be.

The usual pattern is COUNT(*) instead of COUNT(town). The latter implies checking town for being not null, which is unnecessary in this context.

Try the following code:

select ccode, count(empno) 
from company_details 
group by ccode;

If you want to select town and total user count, you can use this query below:

SELECT Town, (SELECT Count(*) FROM User) `Count` FROM user GROUP BY Town;