Adding total of a row and a col given certain conditions

Question

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.

Looks like you want to use a `ROLLUP` with filters on grouping sets and then conditional aggregation, rather than `PIVOT`, to get the totals. — MT0, Nov 15 '22 at 14:35

score 0 · Answer 1 · answered Nov 15 '22 at 15:05

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

working fantastic, one thing is that Im trying to understand why status was casted to char, because I need to build this query in a string builder and those lines : COUNT(CASE user_type WHEN 1 THEN 1 END) AS "1" prevent me from doing so because of the parentheses. — Gal Mor, Nov 16 '22 at 06:26
@גלמור In a `CASE` expression, all the outputs need to be the same data type and you are outputting strings for the total and sub-total rows so you need to convert the status from a number to a string as well, which requires `TO_CHAR`. As for the quoted identifiers, either escape the double quotes when you use `StringBuilder` or use an identifier that does not require quoting [fiddle](https://dbfiddle.uk/qTGaIrXk). — MT0, Nov 16 '22 at 08:38

d r · Answer 2 · 2022-11-15T17:46:53.797

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...

Adding total of a row and a col given certain conditions

2 Answers2

Linked