Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Getting a summation of results

User_OMEF8Jan 8 2021

Hello Everyone,
I have a working query and I am using a pivot. What I want to do is add another column that will sum my columns, but I am having trouble. Here is my query.

SELECT * FROM ( 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 to_char(a.id) = b.id
                )
        ) pivot (  
             count(*)
             for(DATES) in ( '2021-01-01', '2021-01-02', '2021-01-03' )
            )
ORDER BY 1;

This is result that I get from the query.

DOC_TYPE | '2021-01-01' | '2021-01-02' | '2021-01-03'
TYPE_1   |           1  |           3  |           2
TYPE_2   |           10 |           20 |           5
TYPE_3   |          100 |          500 |          60

I would like to add the sum at the end and it should be a summation of 2021-01-01 through 2021-01-03.
Desired results would look 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

I tried adding this to my query, but it gave me an error of "missing right parenthesis".

...
pivot (  
             count(*)
             for(DATES) in ( '2021-01-01', '2021-01-02', '2021-01-03' )
             sum( q'['2021-01-01']' +  q'['2021-01-02']' +  q'['2021-01-03']')
)
...

Also tried

...
pivot (  
             count(*)
             for(DATES) in ( '2021-01-01', '2021-01-02', '2021-01-03' )
             sum('''2021-01-01''' + '''2021-01-02''' + '''2021-01-03''')
)
...

I am assuming I am referencing the column wrong, but I am not sure how to reference it. Thanks in advance for all the help.
This post has been answered by Frank Kulash on Jan 8 2021
Jump to Answer

Comments

PuneetRekhade
Default Weblogic EM console does provide with in-built testing functionality.
Else, you can give a shot to "SOAPUI (http://www.soapui.org/)" tool.

Puneet.
Eric Elzinga
we also use soapui tests for all the integration tests we create
also have a look at soapuipro, lot's of nice functionality

you can easily connect to the database to verify data, or call other webservices to verify if some save action really got saved, and by retrieving all the data you have several different activities to assert your data (groovy, assertions, etc)
we also use some testing support database packages so we can easily trigger some situations we want to test

for loadtesting we use loadui which will eventually use the soapui projects to generate the load, in loadui you can then create production-like situaties (different amount of calls for different services etc)
1 - 2

Post Details

Added on Jan 8 2021
#query, #sql, #sqldeveloper
10 comments
806 views
1 mention