將前 5 名之後剩餘的“其他”相加

我有下面的查詢，它返回每個經紀公司的前 5 名總和

SELECT * 
FROM  (SELECT b.brokerage_name AS Brokerage, 
             SUM(c.cost)      AS Cost 
      FROM   (tblquotesnew q 
              left join tblbrokeragesnew b 
                     ON q.brokerage_id = b.id) 
             left join tblcommissionnew c 
                    ON q.quoteid = c.quote_id 
      WHERE  c.calc_date >= Trunc(SYSDATE, 'iw') 
             AND c.calc_date <= Trunc(SYSDATE, 'iw') + 7 - 1 / 86400 
      GROUP  BY b.brokerage_name 
      HAVING SUM(c.cost) IS NOT NULL 
      ORDER  BY SUM(c.cost) DESC) 
WHERE  ROWNUM <= 5 
ORDER  BY ROWNUM; 

在同一個查詢中，是否有可能為其他返回第六行，這將是剩餘 c.cost 的總和？

您可以使用 CTE (WITH子句)，它預先計算所有查詢SUM(Costs)，然後是UNION ALL兩個查詢：一個是 CTE 中的“前 5 個”；另一個與SUM其餘的：

WITH q2 AS
(
SELECT
   q1.*, row_number() over (order by Cost DESC) AS r
FROM
   (
   SELECT 
       q.brokerage_id, SUM(c.cost) AS Cost
   FROM   
       tblquotesnew q 
       join tblcommissionnew c  ON q.quoteid = c.quote_id 
   WHERE  
       c.calc_date >= Trunc(SYSDATE, 'iw')  AND c.calc_date <= Trunc(SYSDATE, 'iw') + 7 - 1 / 86400 
   GROUP BY 
       q.brokerage_id 
   HAVING 
       SUM(c.cost) IS NOT NULL
   ) q1
ORDER BY 
   Cost DESC
)
-- The "top 5"
SELECT 
   q2.r AS rank, b.brokerage_name AS Brokerage, q2.Cost
FROM 
   q2 LEFT JOIN tblbrokeragesnew b ON b.id = q2.brokerage_id
WHERE
   r <= 5
UNION ALL
-- The sum() of the rest (notice there is no group by; we add them all)
SELECT
   6 AS rank, 'Others', SUM(Cost)
FROM
   q2
WHERE
   r > 5 
ORDER BY
   rank ;

您將得到如下所示的結果：

排名 | 經紀 | 成本
---: | :---------- | ---:
1 | 經紀1 | 8351
2 | 經紀 5 | 2231
3 | 經紀業務 4 | 2227
4 | 經紀業務 3 | 2223
5 | 經紀業務 7 | 2210
6 | 其他 | 4420

---

筆記：

1. 您的LEFT JOINontblcommissionnew實際上是一個INNER JOIN，因為您在 where 中使用 tblcommissionnew 以消除所有空值。
2. 直到最後，您都不需要JOIN使用桌子。tblbrokeragesnewOracle 可能足以對其進行優化，但明確地執行它會更乾淨。（我假設這tblbrokeragesnew.id是一個PK）。

您可以在此處**的dbfiddle檢查所有內容（包括我假設的表定義）

差不多一樣。我的範例使用 hr.EMPLOYEES 展示表：

select case when rn <= 5 then to_char(department_id) else 'Others' end dept,
      sum(ss) total 
 from (
        select department_id, sum(salary) ss,
               row_number() over ( order by  sum(salary) desc ) rn 
          from employees
         group by department_id
         order by ss desc
      )
group by 
 case when rn <= 5 then to_char(department_id) else 'Others' end 
order by max(ss) desc;

引用自：https://dba.stackexchange.com/questions/177998