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

Question

I have an SQL SELECT query that also uses a GROUP BY, I want to count all the records after the GROUP BY clause filtered the resultset.

Is there any way to do this directly with SQL? For example, if I have the table users and 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
New York	58
Athens	58

you mean you want your result set to have 2 counts one for towns and one for users? — Leslie, Apr 27 '10 at 16:37
So you want one row for each town, and in each row, column 2 contains the total count of all users? So column 2 has the *same* value for each row? If you edit to include sample data and required output we will be able to give you exactly what you want. — AakashM, Apr 28 '10 at 07:33
Related question: http://stackoverflow.com/questions/5146978/count-number-of-records-returned-by-group-by — milkovsky, Jun 11 '14 at 12:29
_Caveat to readers: Most of the answers fail to provide an answer for the query as updated._ — Rick James, Jul 07 '18 at 19:58

score 337 · Accepted Answer · edited Sep 20 '22 at 19:28

This will do what you want (list of towns, with the number of users in each):

SELECT `town`, COUNT(`town`)
FROM `user`
GROUP BY `town`;

You can use most aggregate functions when using a GROUP BY statement (COUNT, MAX, COUNT DISTINCT etc.)

Update: You can declare a variable for the number of users and save the result there, and then SELECT the value of the variable:

DECLARE @numOfUsers INT
SET @numOfUsers = SELECT COUNT(*) FROM `user`;

SELECT DISTINCT `town`, @numOfUsers FROM `user`;

score 192 · Answer 2 · edited Nov 01 '22 at 13:03

192

You can use COUNT(DISTINCT ...) :

SELECT COUNT(DISTINCT town) 
FROM user

edited Nov 01 '22 at 13:03

General Grievance

4,555
31
31
45

answered Jun 11 '14 at 12:29

milkovsky

8,772
4
28
32

3

I think they mean if you put COUNT(DISTINCT town) in the WHERE clause. That is because it is an aggregate function and needs to be provided in the HAVING clause. This SQL query is misleading to some as SELECT COUNT(DISTINCT town) turns into an implicit GROUP BY, due to both the COUNT and DISTINCT keywords, each keyword on their own would also implicitly group. – A. Greensmith May 24 '16 at 06:49
Thanks. Upvote. Exactly what is needed - group + count in one op and get a single row in result. – Green Sep 20 '17 at 03:23
Shit.. I didn't know that was possible! – Hugo S. Mendes Oct 27 '17 at 20:15
You have my respect master! Thank you very much. Such simple solution. – Narayan Singh Nov 30 '17 at 13:12
Is performing slow when use DISTINCT – Naveen Apr 18 '18 at 08:53
1

That query comes back with simply "3", not a list of the 3 towns, nor the total user count. – Rick James Jul 07 '18 at 19:57
@RickJames right, I have deleted that answer. You can use the solution from "Oded" or "Violendy Firdaus". – milkovsky Jul 19 '18 at 08:37
1

@milkovsky - Thanks for deleting that wrong answer. I am on a campaign to get rid of the rest of the "wrong answers". _This_ answer is still wrong -- the OP is asking for 2 columns; your query gives only one. – Rick James Jul 19 '18 at 16:07
@RickJames I agree, this answer does not exactly fit to the required results. Quite some people found it useful though. I could change this answer, but other answers already contain correct queries. Do I have to delete it? – milkovsky Jul 23 '18 at 08:38
2

@milkovsky - No you don't have to delete it. I just find it irritation that this Question plus the many Answers have bifurcated into solving two _different_ problems. Your answer deviates in two ways -- no `town` column, and it `COUNTs` the wrong thing (town instead of user). – Rick James Jul 23 '18 at 16:53
Showing the output would be nice, so we know better what problem this solves. – flow2k Jun 23 '21 at 04:31
Doesn't work with `group by` style queries :| – rogerdpack Jul 06 '21 at 18:48
1

@rogerdpack `COUNT(DISTINCT ...)` does work with `GROUP BY`, just make sure your grouping field is present in the `SELECT` statement. Example: `SELECT COUNT(DISTINCT town), street FROM user GROUP BY street` – milkovsky Nov 11 '21 at 13:20

score 45 · Answer 3 · edited Oct 14 '16 at 23:36

45

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

edited Oct 14 '16 at 23:36

Kamil Slowikowski

4,184
3
31
39

answered Oct 15 '13 at 00:52

ZhenYu Wang

1,077
2
11
13

6

needs alias otherwise wouldnt work in mysql. select count(*) from( ) agr – amas Jan 09 '14 at 06:54

score 13 · Answer 4 · answered Jul 07 '18 at 19:55

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.

"The usual pattern is COUNT(*) instead of COUNT(town). The latter implies checking town for being not null, which is unnecessary in this context" Thank you for actually mentioning that, was having a tough time figuring the difference! — Ayush, Oct 19 '21 at 18:02
@Ayush - You are welcome. It seems that most users have not yet discovered that subtle distinction. In some other cases, one wants `COUNT(DISTINCT town)`. — Rick James, Apr 19 '23 at 21:18

score 6 · Answer 5 · answered Apr 28 '10 at 07:02

6

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

answered Apr 28 '10 at 07:02

Tommi

8,550
5
32
51

something like the last one would work, but I wanted to see if there is any other solution.. – Stavros Apr 28 '10 at 07:47
And cannot you use the first (analytic function) option? What database platform are you using? – Tommi Apr 28 '10 at 08:37
@Stavros: The last one is slow – Michael Buen Apr 28 '10 at 11:37

score 4 · Answer 6 · answered Dec 18 '13 at 18:22

4

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

answered Dec 18 '13 at 18:22

Renato Probst

5,914
2
42
45

score 4 · Answer 7 · answered Oct 06 '14 at 05:47

4

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

answered Oct 06 '14 at 05:47

Jur P

103
6

score 2 · Answer 8 · answered Mar 12 '16 at 00:31

2

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

answered Mar 12 '16 at 00:31

Marcus

29
1

5

There is nothing wrong with answering old posts. However, please include an explanation of your code as well as the code itself. – Shelvacu Mar 15 '16 at 17:42
The MySQL equivalent (`IFNULL` instead of `ISNULL`) leads to different numbers for each town; the user wanted the total. According to the Question, 58, not 174, is the total. – Rick James Jul 07 '18 at 19:52

score 1 · Answer 9 · edited Oct 14 '14 at 07:19

1

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;

edited Oct 14 '14 at 07:19

Sal00m

2,938
3
22
33

answered Oct 14 '14 at 06:54

Violendy Firdaus

19
2

This assumes (perhaps reasonably) there there are no duplicate "users" in `User`. – Rick James Jul 19 '18 at 16:08

score 1 · Answer 10 · answered Dec 05 '17 at 12:27

1

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

answered Dec 05 '17 at 12:27

Prakash

100
10

Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its long-term value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Toby Speight Dec 05 '17 at 12:53

score 0 · Answer 11 · edited Apr 06 '15 at 09:47

0

Try the following code:

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

edited Apr 06 '15 at 09:47

JodyT

4,324
2
19
31

answered Apr 06 '15 at 09:40

balajibran

62
8

we use this code to find how many total employee for present day calc in each and every ccode ( company code ) example : count(empno) is 1839 for ccode 1 and count(empno) is 9421 for ccode 47. – balajibran Apr 07 '15 at 06:52

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

11 Answers11

Linked

Related