Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Follow-up to getting a summation from results

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?
EDIT:
I tried to use something called grouping set, but I must have the wrong syntax since it is not working.
Best Answer
-
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.
Answers
-
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.
-
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!
-
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
-
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.
-
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.
-
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?
-
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.
-
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!
-
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.
-
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.)