mysql 查询按组和总计数获取计数

本文介绍了mysql 查询按组和总计数获取计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个表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;