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)