当我已经计算总行数时，如何计算mysql中分组行的数量 [英] How to count the number of grouped rows in mysql when I already count the total rows

问题描述

我有下表，我想做以下事情:

I have the below table and I want to do the following:

1. 计算每个项目在表格中出现的次数

1. Count the number of times each item appears in the table

计算DISTINCT个项目

按名称对项目进行分组

+-------+---------+
|  id   |  names  |
+-------+---------+
|   1   |  Apple  |
|   2   |  Orange | 
|   3   |  Grape  | 
|   4   |  Apple  | 
|   5   |  Apple  | 
|   6   |  Orange | 
|   7   |  Apple  | 
|   8   |  Grape  |  
+-------+---------+

对于1.和3.点，我有以下查询效果很好:

For the 1. and 3. points I have the following query which works quite well:

SELECT * , 
   COUNT(names) as count_name, 
   FROM tbl_products WHERE type = '1' 
   GROUP BY names

所以我得到了

Apple  (4)
Orange (2)
Grape  (2)

现在，我还想计算按行分组的数量，并添加一行以计算不同的元素，但是由于MySQL接受查询但无法输出结果，因此存在一些问题:

Now I want to also count the number of grouped by rows and added a line to count the distinct elements, however there is some problem, since MySQL accepts the query but cannot output a result:

SELECT * , 
   COUNT(names) as count_name, 
   COUNT(DISTINCT names) as count_total 
   FROM tbl_products WHERE type = '1' 
   GROUP BY names

任何人都可以建议出什么问题吗?

Can anyone advice what might be the problem?

为了获得更大的权限，我想获得一个像这样的表:

For more clearance I want to get a table like this:

    +-------+---------+------------+-------------+
    |  id   |  names  |  count_ctg | count_total |
    +-------+---------+------------+-------------+
    |   1   |  Apple  |      4     |      3      |
    |   2   |  Orange |      2     |      3      |
    |   3   |  Grape  |      2     |      3      |
    +-------+---------+------------+-------------+

推荐答案

为什么不只使用您正在使用的查询:

Why not just use the query you are using:

SELECT * , 
COUNT(names) as count_name, 
FROM tbl_products WHERE type = '1' 
GROUP BY names

此查询可实现所有三个目标.

This query achieves all three objectives.

1)在count_name中获得每个名称值的数量计数.

1) You get a count of the number of each name value in count_name.

2)不同的names值的数量将等于结果集中的行数，因为您要按names进行分组.几乎任何客户端MySQL数据库连接库都可以使您检索此值.

2) The number of distinct names values will be equal to the number of rows in the result set , since you are grouping by names. Pretty much any client-side MySQL DB connection library will enable you to retrieve this value.

3)通过显式使用GROUP BY names

当然，结果集中id的值是没有意义的，您可能只想选择names和count_names.

Of course the value for id in the result set is meaningless, you may want to only select names and count_names.

这篇关于当我已经计算总行数时，如何计算mysql中分组行的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！