mysql - SQL - 带 LIMIT 的 SUM UP 查询

Question

我不知道如何解释我的问题，所以请耐心等待..

我现在的 SQL 查询是:

SELECT Count(CATEGORY),  COUNT(INCIDENT_ID), CATEGORY,

ROUND(Count(CATEGORY) * 100 / (SELECT Count(*) 
                              FROM   incident_view 
                              WHERE 
( create_month = Month(Now() - 
                INTERVAL 1 month) ) 
AND ( create_year = Year( 
      Now() - INTERVAL 1 month) ) 
AND customer_company_name = "Company"
   ), 1) AS Percentage
FROM   incident_view 
WHERE  ( create_month = Month(Now() - INTERVAL 1 month) ) 
 AND ( create_year = Year(Now() - INTERVAL 1 month) ) 
AND customer_company_name = "Company"
GROUP BY CATEGORY
ORDER BY COUNT(INCIDENT_ID) DESC
limit 6, 1525125215;

我的数据库有 10 个类别，此查询检索记录最多 7-10 条的类别。

如果我使用查询，我会从数据库收到 4 个最低的记录...但我想检索 4 个最低记录作为一个名为“其他”的记录，其中包含查询中的所有数据(SUM)。

目前的示例(结果):

 Count(Category) | Category | Percentage
    10               Cat1       5.0
     15              Cat2       3.0
    20               Cat 3      4.0

但我希望结果看起来像:

Count(Category) | Category | Percentage
   45             Other       12.0

有什么办法可以达到这个结果吗？

非常感谢任何帮助。

干杯

Answer 1

你想要另一个subquery 。没有 SQLFiddle，我无法测试它，但它类似于:

Select Sum(category_count), 'other', sum(percentage) 
from (
SELECT Count(CATEGORY) as category_count,  COUNT(INCIDENT_ID), CATEGORY,

ROUND(Count(CATEGORY) * 100 / (SELECT Count(*) 
                              FROM   incident_view 
                              WHERE 
( create_month = Month(Now() - 
                INTERVAL 1 month) ) 
AND ( create_year = Year( 
      Now() - INTERVAL 1 month) ) 
AND customer_company_name = "Company"
   ), 1) AS Percentage
FROM   incident_view 
WHERE  ( create_month = Month(Now() - INTERVAL 1 month) ) 
 AND ( create_year = Year(Now() - INTERVAL 1 month) ) 
AND customer_company_name = "Company"
GROUP BY CATEGORY
ORDER BY COUNT(INCIDENT_ID) DESC
limit 6, 1525125215) as original_query;