mysql计算group的数量_mysql 统计 group by 之后的 group 的个数

如果将 count(*) 和 group by 一起使用，count(*) 统计的将会是每个 group 里面的行数，而不是 group 的个数。

如果你想统计 group 的个数，需要将 group by 查询放到子查询里面，然后在主查询里面再使用 count(*)。

如下所示，第一个查询的 count(*) 统计的只是特定的 Category 和 Year 下的总行数，而第二个查询才是第一个查询的 group 的个数。

mysql> SELECT tag AS Category, YEAR(created) AS Year,

COUNT(*) AS Counts FROM fyi_links GROUP BY tag,

YEAR(created);

+----------+------+--------+

| Category | Year | Counts |

+----------+------+--------+

| DBA | 2005 | 1 |

| DBA | 2006 | 2 |

| DEV | 2004 | 1 |

| DEV | 2006 | 1 |

| SQA | 2003 | 1 |

| SQA | 2006 | 1 |

+----------+------+--------+

6 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM (

SELECT tag AS Category, YEAR(created) AS Year,

COUNT(*) AS Counts FROM fyi_links GROUP BY tag,

YEAR(created) ) groups;

+----------+

| COUNT(*) |

+----------+

| 6 |

+----------+

1 row in set (0.00 sec)