SQl-如何將Total列和行添加到我的sql結果集中

我想將“總計”列和行添加到我的查詢結果中。我怎樣才能實作它？這是我的查詢：

SELECT system,
       SUM(CASE
             WHEN filename LIKE '0601%' OR filename LIKE '0401%' OR
                  filename LIKE '0901%' THEN
              (amt / 100)
             ELSE
              0
           END) AS ci,
       SUM(CASE
             WHEN file_date = '202112' and filename LIKE '0501%' OR
                  filename LIKE '1101%' THEN
              (amt / 100)
             ELSE
              0
           END) AS bi,
       SUM(CASE
             WHEN file_date = '202112' and filename LIKE '0101%' THEN
              (amt / 100)
             ELSE
              0
           END) AS ac
  FROM tab1
 GROUP BY system

這是我的結果：

期望：要添加的總行和列并用總和填充

uj5u.com熱心網友回復：

您可以使用子查詢分解，例如

WITH t AS
(
 <your current query>
)
SELECT t.*, ci   bi   ac AS 'Total' 
  FROM t
UNION ALL  
SELECT 'Total', SUM(ci), SUM(bi), SUM(ac), SUM(ci)   SUM(bi)   SUM(ac)
  FROM t

uj5u.com熱心網友回復：

如果您只是希望 amt 列作為結果中的總列，您可以在查詢末尾添加它。否則，如果您希望總數為 CI BI AC，您可以使用以下查詢 -

SELECT
  SYSTEM,
  SUM(CASE WHEN filename like '0601%' or filename like '0401%' 
             or filename like '0901%' then (amt/100) ELSE 0 END
  ) AS CI, 
  SUM(CASE WHEN file_date='202112' and filename like '0501%'
             or filename like '1101%' then (amt/100) ELSE 0 END
  ) AS BI,
  SUM(CASE WHEN file_date='202112' and filename like '0101%'
      then (amt/100) ELSE 0 END
  ) AS AC,
  SUM(CASE WHEN filename like '0601%' or filename like '0401%' 
             or filename like '0901%' then (amt/100) ELSE 0 END
  )  
  SUM(CASE WHEN file_date='202112' and filename like '0501%'
             or filename like '1101%' then (amt/100) ELSE 0 END
  )  
  SUM(CASE WHEN file_date='202112' and filename like '0101%'
      then (amt/100) ELSE 0 END
  ) AS TOTAL
FROM tab1 
GROUP BY system

uj5u.com熱心網友回復：

如果是 SQL*Plus，你會

SQL> break on report
SQL> compute sum of sum_sal on report
SQL>
SQL> select deptno, sum(sal) sum_sal
  2  from emp
  3  group by deptno
  4  order by deptno;

    DEPTNO    SUM_SAL
---------- ----------
        10       8750
        20      10875
        30       9400
           ----------
sum             29025

SQL>

但是 - 現在沒有多少人使用它。

除此之外，您可能group by rollup在任何地方都可以使用（我的意思是，不僅僅是在 SQL*Plus 中）。

SQL> select deptno, sum(sal) sum_sal
  2  from emp
  3  group by rollup(deptno)
  4  order by deptno;

    DEPTNO    SUM_SAL
---------- ----------
        10       8750
        20      10875
        30       9400
                29025

SQL>

uj5u.com熱心網友回復：

關鍵是有一個嵌套的選擇陳述句來首先獲取您的總和。這樣您就可以將結果用于總計。

SELECT system, ci, bi, ac, ac   bi   ci as total
FROM(
       SELECT
       SUM(CASE
             WHEN filename LIKE '0601%' OR filename LIKE '0401%' OR
                  filename LIKE '0901%' THEN
              (amt / 100)
             ELSE
              0
           END) AS ci,
       SUM(CASE
             WHEN file_date = '202112' and filename LIKE '0501%' OR
                  filename LIKE '1101%' THEN
              (amt / 100)
             ELSE
              0
           END) AS bi,
       SUM(CASE
             WHEN file_date = '202112' and filename LIKE '0101%' THEN
              (amt / 100)
             ELSE
              0
           END) AS ac
           FROM tab1) t
      )
 GROUP BY system

標籤：sql 甲骨文

上一篇：GoogleBigQuery中的去相關子查詢？

下一篇：SQL：如何處理NULL和PARTITIONBY？