How to use count and group by at the same select statement
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 |
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`;
You can use COUNT(DISTINCT ...)
:
SELECT COUNT(DISTINCT town)
FROM user
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
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.
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
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 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
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 select town and total user count, you can use this query below:
SELECT Town, (SELECT Count(*) FROM User) `Count` FROM user GROUP BY Town;
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
Try the following code:
select ccode, count(empno)
from company_details
group by ccode;
精彩评论