按(前5名)& (所有其他)的计数 [英] Group by Count of (Top 5) & Count of (All Other)

问题描述

我有一个需要编辑的查询.当前，它返回2列数据，案件标签和从昨天开始的前7天期间处理的计数(或案件总数).我需要更改此输出，以使输出中只有6个标签(即-始终为6行数据).这些行必须是前5个标签，其余标签的总和作为第6个标签(称为其他").这是因为此输出被馈送到PHP脚本，该脚本在基于Web的平台上显示数据.

I have a query that I need to edit. Currently, it returns 2 columns of data, a case label and the count (or total number of cases) handled during the previous 7 day period starting yesterday. I need to change this output so that only 6 labels are in the output (i.e - always 6 rows of data). These rows need to be the top 5 labels and the sum of the remaining labels as the 6th label (called "Other"). This is because this output is fed to a PHP script that presents the data on a web-based platform.

最后，在这里说明我需要的输出以及下面的查询的表格.

Finally, to illustrate here is a table of the output I need as well as the query below.

+-----------+---------------+  
| CaseLabel | CasesResolved |  
+-----------+---------------+  
| Label1    |            20 |  
| Label2    |            18 |
| Label3    |            10 |
| Label4    |             9 |
| Label5    |             7 |
| Other     |            12 |
+-----------+---------------+

在此先感谢您的帮助！ :-)

Thanks in advance for any help! :-)

运行MySQL 5.096

Running MySQL 5.096

SELECT
    deskcases.Labels,
    COUNT(deskcases.Labels)AS CaseCount
FROM
    deskcases
WHERE
    deskcases.Labels NOT LIKE ''
AND deskcases.Labels NOT LIKE '%SPAM%'
AND deskcases.Labels NOT LIKE '%Online Orders%'
AND deskcases.Labels NOT LIKE '%Internal SPAM%'
AND deskcases.`Case Status` LIKE 'Resolved'
AND deskcases.`Resolved At` > CURDATE()- INTERVAL 7 DAY
GROUP BY
    deskcases.Labels
ORDER BY
    CaseCount DESC

推荐答案

在MySQL中，表达这一点的最简单方法可能是使用临时表:

In MySQL, probably the easiest way to express this is by using a temporary table:

create temporary table temp as (
    id int not null auto_increment,
    CaseLabel varchar(255),
    CasesResolved int
);

insert into temp(CaseLabel, CasesResolved)
    SELECT deskcases.Labels, COUNT(deskcases.Labels)AS CaseCount
    FROM deskcases
    WHERE deskcases.Labels NOT LIKE ''
          AND deskcases.Labels NOT LIKE '%SPAM%'
          AND deskcases.Labels NOT LIKE '%Online Orders%'
          AND deskcases.Labels NOT LIKE '%Internal SPAM%'
          AND deskcases.`Case Status` LIKE 'Resolved'
          AND deskcases.`Resolved At` > CURDATE()- INTERVAL 7 DAY
    GROUP BY deskcases.Labels
    ORDER BY CaseCount DESC;

select (case when id <= 5 then caselabel else 'Other' end),
       SUM(casesResolved) as CasesResolved
from temp
group by (case when id <= 5 then caselabel else 'Other' end)
order by MAX(id) desc

临时表中的id列在每行上添加一个行号.在任何其他真实数据库中，您都可以使用row_number()函数，但是MySQL不支持该函数.

The id column in the temporary table adds a row number onto each row. In any other real database, you would use the row_number() function, but MySQL does not support that.

这篇关于按(前5名)& (所有其他)的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！