mysql group by和count rows问题

let's say mysql is something like this

让我们说mysql是这样的

select x,y 
from xx 
group by y

i want to know how many rows that select will get, i tried to use count but it will n't return all results since i'm using group by.

我想知道选择了多少行,我试图使用count但是它不会返回所有结果,因为我正在使用group by。

how to do that?

怎么做?

Thanks

3 个解决方案

#1

You can wrap your query like so:

你可以这样包装你的查询:

SELECT COUNT(*) FROM
  (select x,y  
    from xx  
    group by y) sub;

#2

Suppose you have a table with the content below:

假设您有一个包含以下内容的表:

 -------------------
| ID | NAME | GROUP |
+-------------------+
| 1  |  A   |  1    |
+-------------------+
| 2  |  B   |  2    |
+-------------------+
| 3  |  C   |  2    |
+-------------------+
| 4  |  D   |  3    |
+-------------------+
| 5  |  E   |  1    |
+-------------------+
| 6  |  F   |  3    |
+-------------------+

The following self LEFT JOIN counts the number of distinct values in GROUP.

以下自LEFT JOIN计算GROUP中不同值的数量。

SELECT COUNT(*)
FROM table AS t1
LEFT JOIN table AS t2 ON t2.GROUP = t1.GROUP AND t2.ID > t1.ID
WHERE t2.id IS NULL;

What this query does is find out, for each group, the element with the highest id.

这个查询的作用是为每个组找出id最高的元素。

#3

You can check my ans https://*.com/a/36449637/2439715

你可以查看我的ans https://*.com/a/36449637/2439715

SELECT 
    sum(1) as counttotal
FROM (
    Your query with group by operator
) as T

#1