Forum Stats

  • 3,783,669 Users
  • 2,254,818 Discussions
  • 7,880,513 Comments

Discussions

Follow-up to getting a summation from results

User_OMEF8
User_OMEF8 Member Posts: 126 Blue Ribbon
edited Jan 14, 2021 6:58PM in SQL & PL/SQL

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

«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,424 Red Diamond

    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
  • User_OMEF8
    User_OMEF8 Member Posts: 126 Blue Ribbon

    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
    Paulzip Member Posts: 8,544 Blue Diamond

    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
    
    User_OMEF8
  • User_OMEF8
    User_OMEF8 Member Posts: 126 Blue Ribbon

    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
    User_OMEF8 Member Posts: 126 Blue Ribbon
    edited Jan 14, 2021 8:40PM

    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
    Frank Kulash Member, Moderator Posts: 41,424 Red Diamond

    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
  • User_OMEF8
    User_OMEF8 Member Posts: 126 Blue Ribbon

    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
    Frank Kulash Member, Moderator Posts: 41,424 Red Diamond

    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.

    User_OMEF8
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,424 Red Diamond

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

    User_OMEF8