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
– Leslie, Commented Apr 27, 2010 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
– AakashM, Commented Apr 28, 2010 at 7:33
Caveat to readers: Most of the answers fail to provide an answer for the query as updated. — Rick James
– Rick James, Commented Jul 7, 2018 at 19:58

Skully · Accepted Answer · 2022-09-20 19:28:58Z

355

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`;

edited Sep 20, 2022 at 19:28

Skully

3,1743 gold badges29 silver badges43 bronze badges

answered Apr 27, 2010 at 15:17

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

General Grievance · Accepted Answer · 2022-11-01 13:03:54Z

195

You can use COUNT(DISTINCT ...) :

SELECT COUNT(DISTINCT town) 
FROM user

edited Nov 1, 2022 at 13:03

General Grievance

5,12039 gold badges40 silver badges60 bronze badges

answered Jun 11, 2014 at 12:29

milkovsky

8,8724 gold badges31 silver badges33 bronze badges

13 Comments

A. Greensmith Over a year ago

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.

Rick James Over a year ago

That query comes back with simply "3", not a list of the 3 towns, nor the total user count.

Rick James Over a year ago

@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 Over a year ago

@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).

milkovsky Over a year ago

@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

|

Kamil Slowikowski · Accepted Answer · 2016-10-14 23:36:21Z

49

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, 2016 at 23:36

Kamil Slowikowski

4,7143 gold badges35 silver badges43 bronze badges

answered Oct 15, 2013 at 0:52

ZhenYu Wang

1,1272 gold badges11 silver badges13 bronze badges

1 Comment

amas Over a year ago

needs alias otherwise wouldnt work in mysql. select count(*) from( ) agr

Rick James · Accepted Answer · 2018-07-07 19:55:21Z

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 - You are welcome. It seems that most users have not yet discovered that subtle distinction. In some other cases, one wants COUNT(DISTINCT town).

Tommi · Accepted Answer · 2010-04-28 07:02:23Z

5

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, 2010 at 7:02

Tommi

8,6185 gold badges35 silver badges51 bronze badges

3 Comments

Stavros Over a year ago

something like the last one would work, but I wanted to see if there is any other solution..

Tommi Over a year ago

And cannot you use the first (analytic function) option? What database platform are you using?

Michael Buen Over a year ago

@Stavros: The last one is slow

Renato Probst · Accepted Answer · 2013-12-18 18:22:45Z

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, 2013 at 18:22

Renato Probst

6,0642 gold badges46 silver badges45 bronze badges

Comments

Jur P · Accepted Answer · 2014-10-06 05:47:31Z

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 6, 2014 at 5:47

Jur P

996 bronze badges

Comments

Marcus · Accepted Answer · 2016-03-12 00:31:04Z

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, 2016 at 0:31

Marcus

291 bronze badge

2 Comments

Shelvacu Over a year ago

There is nothing wrong with answering old posts. However, please include an explanation of your code as well as the code itself.

Rick James Over a year ago

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.

Sal00m · Accepted Answer · 2014-10-14 07:19:31Z

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, 2014 at 7:19

Sal00m

2,9143 gold badges24 silver badges33 bronze badges

answered Oct 14, 2014 at 6:54

Violendy Firdaus

192 bronze badges

1 Comment

Rick James Over a year ago

This assumes (perhaps reasonably) there there are no duplicate "users" in User.

Prakash · Accepted Answer · 2017-12-05 12:27:15Z

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 5, 2017 at 12:27

Prakash

9610 bronze badges

1 Comment

Toby Speight Over a year ago

Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation would greatly improve 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.

JodyT · Accepted Answer · 2015-04-06 09:47:26Z

0

Try the following code:

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

edited Apr 6, 2015 at 9:47

JodyT

4,4222 gold badges22 silver badges31 bronze badges

answered Apr 6, 2015 at 9:40

balajibran

728 bronze badges

1 Comment

balajibran Over a year ago

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.

Collectives™ on Stack Overflow

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

11 Answers 11

Comments

13 Comments

1 Comment

2 Comments

3 Comments

Comments

Comments

2 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

Comments

13 Comments

1 Comment

2 Comments

3 Comments

Comments

Comments

2 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related