mysql 查询按组和总计数获取计数 [英] mysql query for getting count by group and total count

问题描述

我有一个表table1"，如下所示:

I have a table 'table1' as follows:

col1
----
1      
1
2
2
2
3
3

我想从这个表中按组计算和总计数如下:

I want to get count by group and total count from this table as follows:

col1    group_count   total_count
-------------------------------------
1       2             7
2       3             7
3       2             7

我尝试如下:

SELECT col1, group_count, total_count FROM 
(SELECT col1, COUNT(col1) AS group_count FROM table1 GROUP BY col1) Temp1, 
(SELECT COUNT(col1) AS total_count FROM table1) Temp2

优化方法

推荐答案

优化的方法是先计算计数，然后简单地将变量放入你的 select 语句中:

the optimized way is to first calculate the count and then simply put the variable in your select statement:

set @rowCount = (select count(col1) from table1);
select col1, count(col1), @rowCount from table1 group by col1;

查看结果

@Meherzad 给出的方法会多次计算行数.但是，如果您想在单个查询中执行此操作，您可以使用:

The approach given by @Meherzad will calculate the row count many times. But if you want to do this in a single query u can use:

select col1, count(col1), (select count(col1) from table1) rowCountfrom table1 group by col1;

这篇关于mysql 查询按组和总计数获取计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！