Adding total of a row and a col given certain conditions-CodePudding

I have this query :

select pivot_table.*
from   (
  Select STATUS,USER_TYPE
  FROM   TRANSACTIONS tr 
         join TRANSACTION_STATUS_CODES sc on sc.id = tr.user_type
         join TRANSACTION_USER_TYPES ut on ut.id=tr.user_type 
  WHERE  Tr.User_Type between 1 and 5
  And    tr.status!=1
  AND    Tr.Update_Date BETWEEN TO_DATE('2022-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS')
                        AND     TO_DATE('2022-11-13 23:59:59', 'yyyy-mm-dd HH24:MI:SS')
) t
pivot(
  count(user_type)
  FOR user_type IN (1,2,3,5) 
)  pivot_table;

Which gives:

status	1	5
2	3	0
4	13	0
5	1	0
3	5	1
0	4	8

Wanted result:

status	1	5	total
2	3	0	3
4	13	0	13
5	1	0	1
3	5	1	6
0	4	8	12
sum of statuses 2,4,5	17	0	17
sum of all statuses	26	0	35

I have tried adding:

Select STATUS,USER_TYPE,
       count(user_type) as records,
       sum(user_type) over (partition by status) as total

and in the end:

pivot ( sum (records) for user_type in (1,2,3,5)) pivot_table

but logically I am still not there.

CodePudding user response：

You want to use GROUP BY CUBE with conditional aggregation and filters on the grouping sets:

SELECT CASE GROUPING_ID(status, CASE WHEN status IN (2, 4, 5) THEN 1 ELSE 0 END)
       WHEN 0
       THEN TO_CHAR(status)
       WHEN 2
       THEN 'SUB-TOTAL'
       ELSE 'TOTAL'
       END AS status,
       COUNT(CASE user_type WHEN 1 THEN 1 END) AS "1",
       COUNT(CASE user_type WHEN 2 THEN 1 END) AS "2",
       COUNT(CASE user_type WHEN 3 THEN 1 END) AS "3",
       COUNT(CASE user_type WHEN 5 THEN 1 END) AS "5",
       COUNT(*) AS total
FROM   table_name
GROUP BY CUBE(status, CASE WHEN status IN (2, 4, 5) THEN 1 ELSE 0 END)
HAVING GROUPING_ID(status, CASE WHEN status IN (2, 4, 5) THEN 1 ELSE 0 END) IN (0, 3)
OR     (   GROUPING_ID(status, CASE WHEN status IN (2, 4, 5) THEN 1 ELSE 0 END) = 2
       AND CASE WHEN status IN (2, 4, 5) THEN 1 ELSE 0 END = 1 )

Which, for the sample data (representing the output of your many joined tables):

CREATE TABLE table_name (status, user_type) AS
  SELECT 2, 1 FROM DUAL CONNECT BY LEVEL <=  3 UNION ALL
  SELECT 4, 1 FROM DUAL CONNECT BY LEVEL <= 13 UNION ALL
  SELECT 5, 1 FROM DUAL CONNECT BY LEVEL <=  1 UNION ALL
  SELECT 3, 1 FROM DUAL CONNECT BY LEVEL <=  5 UNION ALL
  SELECT 3, 5 FROM DUAL CONNECT BY LEVEL <=  1 UNION ALL
  SELECT 0, 1 FROM DUAL CONNECT BY LEVEL <=  4 UNION ALL
  SELECT 0, 5 FROM DUAL CONNECT BY LEVEL <=  8;

Outputs:

STATUS	1	5	TOTAL
0	4	8	12
3	5	1	6
2	3	0	3
4	13	0	13
5	1	0	1
SUB-TOTAL	17	0	17
TOTAL	26	9	35

You can change the string literals to match the row titles for your desired output.

fiddle

CodePudding user response：

One of the options to do it is to use MODEL clause like this:

Select 
    *
From
    (
        Select Cast(p.STATUS as VARCHAR2(30)) "STATUS",  p."1", p."2", p."3", p."5"
        From pivot_table p Union All
        Select 'Sum of Statuses 2, 4, 5', Null, Null, Null, Null From Dual Union All
        Select 'Sum of All Statuses', Null, Null, Null, Null From Dual
    ) 
MODEL
    Dimension By      (STATUS)
    Measures          ("1", "2", "3", "5", 0 "TOTAL")
    Rules     
          (
              "1"['Sum of All Statuses'] = Sum("1")[ANY],     -- Grand Total of Column 1 = Sum(1) for ANY Status (Dimension)
              "2"['Sum of All Statuses'] = Sum("2")[ANY],
              "3"['Sum of All Statuses'] = Sum("3")[ANY],
              "5"['Sum of All Statuses'] = Sum("5")[ANY],
              --
              "1"['Sum of Statuses 2, 4, 5'] = Sum("1")[STATUS IN('2', '4', '5')],    -- SubTotal of Column 1 = Sum(1) for Status IN 2, 4, 5
              "2"['Sum of Statuses 2, 4, 5'] = Sum("2")[STATUS IN('2', '4', '5')],
              "3"['Sum of Statuses 2, 4, 5'] = Sum("3")[STATUS IN('2', '4', '5')],
              "5"['Sum of Statuses 2, 4, 5'] = Sum("5")[STATUS IN('2', '4', '5')],
              --
              TOTAL[ANY] = "1"[CV()]   "2"[CV()]   "3"[CV()]   "5"[CV()]      -- for each row do the addition of columns with the same Current Value 'CV()' of Status
          )

With the data from your question:

WITH
    pivot_table AS
        (   
       Select 2 "STATUS", 3 "1", 0 "2", 0 "3", 0 "5" From Dual Union All
       Select 4 "STATUS", 13 "1", 0 "2", 0 "3", 0 "5" From Dual Union All
       Select 5 "STATUS", 1 "1", 0 "2", 0 "3", 0 "5" From Dual Union All
       Select 3 "STATUS", 5 "1", 0 "2", 0 "3", 1 "5" From Dual Union All
       Select 0 "STATUS", 4 "1", 0 "2", 0 "3", 8 "5" From Dual 
        )

The result should be:

STATUS	1	5	TOTAL
2	3	0	3
4	13	0	13
5	1	0	1
3	5	1	6
0	4	8	12
Sum of Statuses 2, 4, 5	17	0	17
Sum of All Statuses	26	9	35

Regards...