SQl - How to add a Total column and row to my sql result set-CodePudding

I want to a "total" column and row added to my query result. How can I achieve it? This is my query:

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

and this is my result:

Expectation: Total row and column to be added and populated with sum

CodePudding user response：

You can use Subquery Factoring such as

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

CodePudding user response：

If you simply want amt column as total column in your result, You can add that at the end of your query. Else if you want the total as CI BI AC, You can use below query -

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

CodePudding user response：

If it were SQL*Plus, you'd

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>

but - not many people use it nowadays.

Apart from that, you might group by rollup which works everywhere (I mean, not just in 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>

CodePudding user response：

The key is having a nested select statement to grab your sums first. That way you can use the results for the total.

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