Follow-up to getting a summation from results

User_OMEF8

This follow-up question is directly related to the question that @Frank Kulash answered.

Getting a summation of results — oracle-tech

Is it possible to get a summation for each column? The previous question was for each row. My current results look like this.

DOC_TYPE | '2021-01-01' | '2021-01-02' | '2021-01-03' | SUM_TOTAL
TYPE_1   |           1  |           3  |           2  | 6
TYPE_2   |           4  |           4  |           1  | 9
TYPE_3   |           1  |           1  |           3  | 5

Desired results would be like...

DOC_TYPE | '2021-01-01' | '2021-01-02' | '2021-01-03' | SUM_TOTAL
TYPE_1   |           1  |           3  |           2  | 6
TYPE_2   |           4  |           4  |           1  | 9
TYPE_3   |           1  |           1  |           3  | 5
TOTAL    |           6  |           8  |           6  | 20

Since the data has to be calculated, I think I have to create another query and nest the previous query inside of it. Any thoughts?

create_table.txt

insert_values.txt

EDIT:

I tried to use something called grouping set, but I must have the wrong syntax since it is not working.

Frank Kulash

Hi,

I would use GROUP BY CUBE to get the totals before pivoting, like this:

WITH    date_times_wanted (date_time_id, date_time)    AS
(
    SELECT  1, TO_TIMESTAMP ( '2021-01-01 12.00.00.000000000 PM'
                            , 'YYYY-MM-DD HH:MI:SS.FF PM'
			    ) FROM dual  UNION ALL
    SELECT  2, TO_TIMESTAMP ( '2021-01-02 12.00.00.000000000 PM'
                            , 'YYYY-MM-DD HH:MI:SS.FF PM'
			    ) FROM dual  UNION ALL
    SELECT  3, TO_TIMESTAMP ( '2021-01-03 12.00.00.000000000 PM'
                            , 'YYYY-MM-DD HH:MI:SS.FF PM'
			    ) FROM dual  
)
,    data_to_pivot    AS
(
    SELECT    NVL (w.date_time_id,     0)        AS date_time_id
    ,         NVL (UPPER (a.doc_type), 'TOTAL')  AS doc_type
    ,         COUNT (a.id)                       AS cnt
    FROM      date_times_wanted  w
    LEFT JOIN tablea             a  PARTITION BY (a.doc_type)
    	      			    ON  w.date_time = a.date_time
    WHERE     EXISTS ( 
                         SELECT  1
			 FROM    tableb  b 
                      	 WHERE   b.filename  = 'ABCXYZ'
			 AND     b.id        = TO_CHAR (a.id)
                    ) 
    GROUP BY  CUBE ( w.date_time_id
    	      	   , UPPER (a.doc_type)
		   )  
)
SELECT    *
FROM	  data_to_pivot
PIVOT     (    SUM (cnt)
	  FOR  date_time_id  IN (  1  AS d_2021_01_01  -- or "2021-01-01" ior whatever
	       		     	,  2  AS d_2021_01_02
	       		     	,  3  AS d_2021_01_03
				,  0  AS sum_total
	       		     	)
	  )
ORDER BY  NULLIF (doc_type, 'TOTAL')
;

I suggest using column names that don't need to be quoted, but if you really want the non-standard names, go ahead and use them. The column names that appear in the output have to be hard-coded in either case.

Always use the correct data time. If tablea.date_time is a TIMESTAMP column, then don't try to INSERT string values (like '2021-01-01 12.00.00.000000000 PM') into it. If tableb.id is a VARCHAR2 (20) column, then don't try to INSERT NUMBERs (like 1) into it. Sometimes it might work, and sometimes it might even work the way you want it to work, but it's just asking for trouble.

Frank Kulash

Hi,

Thanks for posting the CREATE TABLE and INSERT statements. Remember why you're posting them: to allow the people who want to help you re-create the problem so they can test their ideas. The people who want to help you won't always be using the same schema- and tablespace names that you use, so it's better if you don't include them in your CREATE TABLE statements. (In fact, you can leave out all the storage information; just include the columns and constraints, if any.)

Desired results would be like...

DOC_TYPE | '2021-01-01' | '2021-01-02' | '2021-01-03' | SUM_TOTAL
TYPE_1   |           1  |           3  |           2  | 6
TYPE_2   |           10 |           20 |           5  | 35
TYPE_3   |          100 |          500 |          60  | 660
TOTAL    |          111 |          523 |          67  | 701

Post the exact results you want from the given sample data. If, given the sample data, the total for 2021-01-01 should be 6, then don't say you want 111. If you actually do want 111, then explain how you get that number from the given data.

User_OMEF8

https://community.oracle.com/tech/developers/discussion/comment/16781389#Comment_16781389

I edited my table of results. I didn't update the attachments, but I will leave the other information off next time. Thanks for your reply!

Paulzip

Yeah, just select from your existing results and union all a total line...

union all
select 'TOTAL', sum("2021-01-01"), SUM("2021-01-02"), SUM("2021-01-03"), SUM(sum_total)
from your_results

Frank Kulash

Hi,

I would use GROUP BY CUBE to get the totals before pivoting, like this:

WITH    date_times_wanted (date_time_id, date_time)    AS
(
    SELECT  1, TO_TIMESTAMP ( '2021-01-01 12.00.00.000000000 PM'
                            , 'YYYY-MM-DD HH:MI:SS.FF PM'
			    ) FROM dual  UNION ALL
    SELECT  2, TO_TIMESTAMP ( '2021-01-02 12.00.00.000000000 PM'
                            , 'YYYY-MM-DD HH:MI:SS.FF PM'
			    ) FROM dual  UNION ALL
    SELECT  3, TO_TIMESTAMP ( '2021-01-03 12.00.00.000000000 PM'
                            , 'YYYY-MM-DD HH:MI:SS.FF PM'
			    ) FROM dual  
)
,    data_to_pivot    AS
(
    SELECT    NVL (w.date_time_id,     0)        AS date_time_id
    ,         NVL (UPPER (a.doc_type), 'TOTAL')  AS doc_type
    ,         COUNT (a.id)                       AS cnt
    FROM      date_times_wanted  w
    LEFT JOIN tablea             a  PARTITION BY (a.doc_type)
    	      			    ON  w.date_time = a.date_time
    WHERE     EXISTS ( 
                         SELECT  1
			 FROM    tableb  b 
                      	 WHERE   b.filename  = 'ABCXYZ'
			 AND     b.id        = TO_CHAR (a.id)
                    ) 
    GROUP BY  CUBE ( w.date_time_id
    	      	   , UPPER (a.doc_type)
		   )  
)
SELECT    *
FROM	  data_to_pivot
PIVOT     (    SUM (cnt)
	  FOR  date_time_id  IN (  1  AS d_2021_01_01  -- or "2021-01-01" ior whatever
	       		     	,  2  AS d_2021_01_02
	       		     	,  3  AS d_2021_01_03
				,  0  AS sum_total
	       		     	)
	  )
ORDER BY  NULLIF (doc_type, 'TOTAL')
;

I suggest using column names that don't need to be quoted, but if you really want the non-standard names, go ahead and use them. The column names that appear in the output have to be hard-coded in either case.

Always use the correct data time. If tablea.date_time is a TIMESTAMP column, then don't try to INSERT string values (like '2021-01-01 12.00.00.000000000 PM') into it. If tableb.id is a VARCHAR2 (20) column, then don't try to INSERT NUMBERs (like 1) into it. Sometimes it might work, and sometimes it might even work the way you want it to work, but it's just asking for trouble.

User_OMEF8

https://community.oracle.com/tech/developers/discussion/comment/16781394#Comment_16781394

Thanks! I did try the "union all", but it kept giving me an error message. ORA-01722: invalid number and I suspect that it has to do with the PIVOT that is used.

User_OMEF8

https://community.oracle.com/tech/developers/discussion/comment/16781398#Comment_16781398

Thanks Frank!! This query did the trick. It is more than what I know so I will have to dissect it and read up on some of the functions you used. I appreciate the tips about the INSERT too. Would it be correct to assume that the "grouping set" doesn't work because of the PIVOT?

Frank Kulash

Hi, @User_OMEF8

Would it be correct to assume that the "grouping set" doesn't work because of the PIVOT?

Sorry, I don't understand the question. What do you mean by "grouping set"? (There is an option for GROUP BY that uses the keyword GROUPING SETS, but I don't see where anyone mentioned it before you did.)

Pivoting is a specific kind of aggregation. GROUP BY is the most general way to do aggregation, including pivoting. SELECT ... PIVOT is a very handy, concise way do do some specific things that you could do with an explicit GROUP BY clause. It is NOT a handy, concise way to do everything that you could do with an explicit GROUP BY clause. In this problem, you want to do some things that SELECT ... PIVOT was designed to do, and some things that are better done with an explicit GROUP BY clause. I showed one way of using SELECT ... PIVOT and GROUP BY wor together, applying SELECT ... PIVOT to the results of GROUP BY.  Paulzip suggested another way of making them work together, applying GROUP BY to the results of SELECT ... PIVOT.

User_OMEF8

https://community.oracle.com/tech/developers/discussion/comment/16781417#Comment_16781417

I was referring to the "group by grouping sets ( (), () )". I had tried to use that, but it did not work the way I had wanted and I suspected that it was because of the PIVOT. Either way, thanks again for the extra information and the help!

Frank Kulash

Hi,

I was referring to the "group by grouping sets ( (), () )". I had tried to use that, ...

If you want to talk about a query you tried, then post the query. It's hard to say what you did wrong without knowing what you did.

Frank Kulash

Hi, @User_OMEF8

Here's another way to do what you requested, doing the SELECT ... PIVOT first and then GROUP BY, without using a separate sub-queries for those two:

   WITH data_to_pivot AS
(
   SELECT UPPER (a.doc_type)                  AS doc_type
   ,      TO_CHAR (a.date_time, 'yyyy-mm-dd') AS dates
   FROM   tablea a
   WHERE  EXISTS ( 
                         SELECT 1
			 FROM  tableb b 
           	         WHERE  b.filename = 'ABCXYZ'
			 AND   b.id        = TO_CHAR (a.id)
           ) 
)
SELECT    NVL (doc_type, 'TOTAL') AS doc_type
,	 SUM (d_1)                AS d_2021_01_01
,	 SUM (d_2)                AS d_2021_01_02
,	 SUM (d_3)                AS d_2021_01_03
,        SUM (d_1 + d_2 + d_3)    AS total
FROM   data_to_pivot
PIVOT   (  COUNT (*)
        FOR (dates)  IN  ( '2021-01-01' AS d_1
	   	   	 , '2021-01-02' AS d_2
			 , '2021-01-03' AS d_3
			 )
     )
GROUP BY ROLLUP (doc_type)
ORDER BY NULLIF (doc_type, 'TOTAL')
;

You could do the same thing with GROUPING SETS instead of ROLLUP. (Anything that ROLLUP can do, GROUPING SETS can do also. Likewise, anything that CUBE can do, GROUPING SETS can do. also. ROLLUP and CUBE are just handy, simpler ways of solving special cases of GROUPING SETS problems.)