时间戳

首页 > 解决方案 > 计算所选列的总数

mysql - 计算所选列的总数

问题描述

我正在尝试检索结果的每一列的总数。以下是我的预期结果和菜单选择表。

菜单选择表：

菜单选择表

查询/预期结果后：

预期结果

问题是我在计算每列的总数时遇到问题。

这是我的查询：


    SELECT menu_option,
        SUM(IF(callStatus='QUEUE', 1, 0)) AS Queue,
        SUM(IF(callStatus='ROUTING' , 1, 0)) AS Routing,
        SUM(IF(callStatus='CALLBACK' , 1, 0)) AS Callback,
        SUM(IF(callStatus='ANSWER' , 1, 0)) AS Answer
    FROM tbl_call_center_menu_selection
    GROUP BY menu_option
    UNION
    SELECT  'Total' AS c, 5 AS a, 2 AS b, 0 AS c, 0 AS d
    FROM tbl_call_center_menu_selection

标签： mysql

解决方案

您可以尝试使用子查询

SELECT menu_option,
        SUM(IF(callStatus='QUEUE', 1, 0)) AS Queue,
        SUM(IF(callStatus='ROUTING' , 1, 0)) AS Routing,
        SUM(IF(callStatus='CALLBACK' , 1, 0)) AS Callback,
        SUM(IF(callStatus='ANSWER' , 1, 0)) AS Answer
    FROM tbl_call_center_menu_selection
    GROUP BY menu_option
    UNION
    SELECT  'Total' , sum(Queue), sum(Routing), sum(Callback), sum(Answer)
    FROM 
    (
        select menu_option,
        SUM(IF(callStatus='QUEUE', 1, 0)) AS Queue,
        SUM(IF(callStatus='ROUTING' , 1, 0)) AS Routing,
        SUM(IF(callStatus='CALLBACK' , 1, 0)) AS Callback,
        SUM(IF(callStatus='ANSWER' , 1, 0)) AS Answer
    FROM tbl_call_center_menu_selection group by menu_option
    )A

推荐阅读