php - 计算一列中有多少个组

Question

使用count()和group by我可以生成属于该组的count。

示例:

数据库

 student_number            sem              SY
    2015-0002            2nd sem          2014-2015
    2015-0002            2nd sem          2014-2015
    2015-0002            2nd sem          2014-2015
    2015-0002            1st sem          2015-2016
    2015-0002            1st sem          2015-2016
    2015-0002            1st sem          2015-2016
    2015-0002            2nd sem          2015-2016
    2015-0002            2nd sem          2015-2016
    2015-0002            2nd sem          2015-2016
    2015-0002            2nd sem          2015-2016

查询

  $sql  = "SELECT *, count(SY) as student_count ";
  $sql .= "FROM studeaccount ";
  $sql .= "WHERE StudentNumber = '2015-0002' ";
  $sql .= "GROUP BY SY, Sem ";
  $sql .= "ORDER BY StudentNumber ASC";

像这样查询上面的输出:

2nd sem : 2014-2015 = 3
1st sem : 2015-2016 = 3
2nd sem : 2015-2016 = 4

上面的结果显示了组的数量以及我想要得到的组的总数。下面的例子我想输出。

Group count = 3 // this is what i want to achieve.

希望这有帮助。

Answer 1

SELECT COUNT(*) FROM
(
    SELECT *, count(SY) as student_count
    FROM studeaccount
    WHERE StudentNumber = '2015-0002'
    GROUP BY SY, Sem
    ORDER BY StudentNumber ASC
 ) t1