首页 文章

如何在同一个select语句中使用count和group by

提问于
浏览 次
189

我有一个具有group by的sql select查询 . 我想在group by语句之后计算所有记录 . 有没有办法直接从sql？例如，拥有一个包含用户的表我想选择不同的城镇和 total 用户数

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

我想要一个包含所有城镇的列，另一个列包含所有行中的用户数 .

拥有3个城镇和58个用户的结果示例如下：

Town         Count
Copenhagen   58
NewYork      58
Athens       58
sql count group-by

11 回答

  • 3

    我知道这是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
    回复于
  • 131

    这将做你想要的（城镇列表，每个城镇的用户数量）：

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

    使用 GROUP BY 时，您可以使用大多数aggregate functions .

    Update （更改问题和评论后）

    您可以为用户数声明一个变量，并将其设置为用户数，然后选择该用户 .

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

SELECT DISTINCT town, @numOfUsers
FROM user
    回复于
  • 2

    另一种方式是：

    /* 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
    回复于
  • 0

    请尝试以下代码：

    select ccode, count(empno) 
from company_details 
group by ccode;
    回复于
  • 0

    使用Oracle，您可以使用分析函数：

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

    您的其他选择是使用子查询：

    select town, count(town), (select count(town) from user) as total_count from user
group by town
    回复于
  • 2

    如果要选择城镇和总用户数，可以使用以下查询：

    SELECT Town, (SELECT Count(*) FROM User) `Count` FROM user GROUP BY Town;
    回复于
  • 31

    您可以在COUNT中使用DISTINCT，就像milkovsky所说的那样

    在我的情况下：

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

    这将把被认为与一个计数相同的user_id的答案投票数量拉出来

    回复于
  • 244

    如果您想使用选择所有查询与计数选项，请尝试此...

    select a.*, (Select count(b.name) from table_name as b where Condition) as totCount from table_name  as a where where Condition
    回复于
  • 2

    你可以使用COUNT(DISTINCT ...)：

    SELECT COUNT(DISTINCT town) 
FROM user
    回复于
  • 1

    如果你想按计数订购（声音很简单，但我找不到如何做到这一点的答案）你可以这样做：

    SELECT town, count(town) as total FROM user
        GROUP BY town ORDER BY total DESC
    回复于
  • 0

    十个未删除的答案;大部分都不做用户要求的 . 大多数答案误读了这个问题，认为每个城镇有58个用户，而不是总共58个 . 即使是少数正确的也不是最佳的 .

    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  
+----------------------------+-------+

    在OP的背景下：

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

    由于 tot 只有一行， CROSS JOIN 并不像其他情况那样庞大 .

    通常的模式是 COUNT(*) 而不是 COUNT(town) . 后者意味着检查 town 是否为null，这在此上下文中是不必要的 .

    回复于

相关问题