如何在MySQL中按英文月份名称分组统计数据？

我正在使用以下PHP脚本查询MySQL数据库中的数据：

<?php    
    $con=mysqli_connect($host,$uname,$password);
    mysqli_select_db($con,$dbname); 
    $query ="SELECT * FROM tb_bankdata WHERE YEAR(last_update) = YEAR(CURDATE())";
    //$query = "SELECT count(*), last_update FROM tb_bankdata WHERE tb_bankdata.anycolumn = 'anycondition' GROUP BY MONTH(last_update)"; --> Not Working
    $result = mysqli_query($con,$query);
     while($row = mysqli_fetch_array($result))
            {
                $last_update = $row['last_update'];
                //$last_update = date('F,Y', strtotime($last_update));
                $progress = $row['progress'];
                $data .= "{last_update:'".$last_update."', progress:'".$progress."'},";
                echo $last_update . " , " . $progress  ."<br>";
            }
?>    

当前输出结果如下：

2022-01-03 11:42:24 , bank_data
2022-01-03 11:42:30 , completed
2022-01-03 11:42:36 , completed
2022-01-03 11:42:41 , bank_data
2022-01-03 11:52:25 , completed
2022-01-03 14:23:41 , bank_data
2022-01-03 16:29:24 , bank_data
2022-01-04 14:46:16 , bank_data
2022-01-05 09:34:44 , bank_data
2022-02-05 16:37:16 , bank_data
2022-02-05 16:37:22 , bank_data
2022-02-14 09:24:57 , completed
2022-02-06 11:35:10 , bank_data
2022-03-31 14:26:49 , bank_data
2022-03-31 14:26:58 , bank_data
2022-03-31 14:27:07 , completed
2022-03-31 14:27:16 , completed
2022-03-31 21:35:48 , bank_data

我需要得到如下格式的输出：

January, completed, 3
January, bank_data, 6
February, completed, 1
February, bank_data, 3
March, completed, 2
March, bank_data, 3 

我曾尝试过转换月份名称的方法以及如下分组查询语句，但都无法得到预期结果：

$query = "SELECT count(*), last_update FROM tb_bankdata WHERE tb_bankdata.anycolumn = 'anycondition' GROUP BY MONTH(last_update)"; 

请问如何编写对应的SQL查询语句以实现数据统计与分组？

你需要按照月份名称和progress字段值进行双重分组，同时统计每组的数量，对应的SQL语句如下：

SELECT 
    MONTHNAME(last_update) AS month_name,
    progress,
    COUNT(*) AS total
FROM tb_bankdata
WHERE YEAR(last_update) = YEAR(CURDATE())
GROUP BY MONTH(last_update), progress
ORDER BY MONTH(last_update), progress DESC;

语句说明： #

1. MONTHNAME(last_update)：将last_update字段的日期转换为英文月份名称（如January、February）。
2. COUNT(*) AS total：统计每组的记录数量。
3. GROUP BY MONTH(last_update), progress：先按月份的数字值分组（保证排序逻辑正确），再按progress的取值分组，这样每个月份下的不同progress状态会被单独统计。
4. ORDER BY MONTH(last_update), progress DESC：先按月份顺序排序，再让completed排在bank_data前面，和你需要的输出格式完全匹配。

对应的PHP输出调整： #

拿到查询结果后，只需要循环输出三个字段即可：

while($row = mysqli_fetch_array($result))
{
    echo $row['month_name'] . ", " . $row['progress'] . ", " . $row['total'] . "<br>";
}

内容的提问来源于stack exchange，提问作者Gatra Di Yoga Tama