按（前5名）和（所有其他）人数统计

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.

我有一个需要编辑的查询。目前,它返回2列数据,案例标签和昨天开始的前7天期间处理的计数(或案件总数)。我需要更改此输出,以便输出中只有6个标签(即 - 总共6行数据)。这些行必须是前5个标签,其余标签的总和必须是第6个标签(称为“其他”)。这是因为此输出被提供给PHP脚本,该脚本在基于Web的平台上显示数据。

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! :-)

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

Running MySQL 5.096

运行MySQL 5.096

MySQL Code:

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

2 个解决方案

#1

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

在MySQL中,可能最简单的表达方式是使用临时表:

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

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.

临时表中的id列在每行上添加一个行号。在任何其他真实数据库中,您将使用row_number()函数,但MySQL不支持它。

#2

-1

One possible option is to use a rank variable.

一种可能的选择是使用秩变量。

The dummy join will initialize the rank and the if will count up to 6.
All this stuff will first get resolved in the inner query, producing stuff like

虚拟连接将初始化排名,if将计数到6.所有这些东西将首先在内部查询中得到解决,产生类似的东西

| Label1    |            20 |  
| Label2    |            18 |
| Label3    |            10 |
| Label4    |             9 |
| Label5    |             7 |
| Label6    |            12 |
| Label7    |               |
| ......

The other query will then collapse this into the desired output.

然后另一个查询将其折叠为所需的输出。

select if(rank=6,"Other",sub.Labels) as Label, SUM(sub.CaseCount) from (
    SELECT
        if(@Rank < 6,@Rank:= @Rank + 1, @Rank) as Rank
        ,deskcases.Labels
        ,COUNT(deskcases.Labels) AS CaseCount
    FROM
        deskcases
    JOIN (@rank:= 0)
    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
) sub
group by sub.rank ASC

Replacing the labelx into "Other" for the last line.

将labelx替换为最后一行的“Other”。

#1