Home » SQL & PL/SQL » SQL & PL/SQL » How to create a grand total from multiple SELECTs
How to create a grand total from multiple SELECTs [message #203852] Thu, 16 November 2006 10:41 Go to next message
malphonse
Messages: 1
Registered: November 2006
Location: Delaware
Junior Member
I have a query with multiple SELECTs and UNIONs. Each statement returns one row: a description and a total. How can I add a Grand Total at the end?

The SQL:
SELECT 'Obj M Actor' table_name, sum(decode (OBJ_M_ACTOR.M_ACTOR_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_ACTOR
UNION
SELECT 'Obj M Connection' table_name, sum(decode (OBJ_M_CONNECTION.M_CNTN_N_LAT , 1, 1, 0)) cnt_log_deletes FROM OBJ_M_CONNECTION
UNION
SELECT 'Obj M Documents' table_name, sum(decode (OBJ_M_DOCUMENTS.M_DOC_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_DOCUMENTS
UNION
SELECT 'Obj M Repository' table_name, sum(decode (OBJ_M_REPOSITORY.M_REPO_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_REPOSITORY
UNION
SELECT 'Obj M Reslink' table_name, sum(decode (OBJ_M_RESLINK.M_RES_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_RESLINK
UNION
SELECT 'Obj M Timestamp' table_name, sum(decode (OBJ_M_TIMESTAMP.M_TMS_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_TIMESTAMP
UNION
SELECT 'Obj M Univcst' table_name, sum(decode (OBJ_M_UNIVCST.M_UNIC_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_UNIVCST
UNION
SELECT 'Obj M Univdbcst' table_name, sum(decode (OBJ_M_UNIVDBCST.M_UNID_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_UNIVDBCST
UNION
SELECT 'Obj M Universes' table_name, sum(decode (OBJ_M_UNIVERSES.M_UNI_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_UNIVERSES


The results:
TABLE_NAME        CNT_LOG_DELETES
Obj M Actor       6
Obj M Connection  3456
Obj M Documents   1040
Obj M Repository  19983
Obj M Reslink     1046
Obj M Timestamp   68
Obj M Univcst     0
Obj M Univdbcst   2342
Obj M Universes   6621
Re: How to create a grand total from multiple SELECTs [message #203853 is a reply to message #203852] Thu, 16 November 2006 10:45 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Wrap a select sum around it:

select
table_name, sum(cnt_log_deletes)
from
(SELECT 'Obj M Actor' table_name, sum(decode (OBJ_M_ACTOR.M_ACTOR_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_ACTOR
UNION
SELECT 'Obj M Connection' table_name, sum(decode (OBJ_M_CONNECTION.M_CNTN_N_LAT , 1, 1, 0)) cnt_log_deletes FROM OBJ_M_CONNECTION
UNION
SELECT 'Obj M Documents' table_name, sum(decode (OBJ_M_DOCUMENTS.M_DOC_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_DOCUMENTS
UNION
SELECT 'Obj M Repository' table_name, sum(decode (OBJ_M_REPOSITORY.M_REPO_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_REPOSITORY
UNION
SELECT 'Obj M Reslink' table_name, sum(decode (OBJ_M_RESLINK.M_RES_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_RESLINK
UNION
SELECT 'Obj M Timestamp' table_name, sum(decode (OBJ_M_TIMESTAMP.M_TMS_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_TIMESTAMP
UNION
SELECT 'Obj M Univcst' table_name, sum(decode (OBJ_M_UNIVCST.M_UNIC_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_UNIVCST
UNION
SELECT 'Obj M Univdbcst' table_name, sum(decode (OBJ_M_UNIVDBCST.M_UNID_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_UNIVDBCST
UNION
SELECT 'Obj M Universes' table_name, sum(decode (OBJ_M_UNIVERSES.M_UNI_N_LAT, 1, 1, 0)) cnt_log_deletes FROM OBJ_M_UNIVERSES) 
GROUP BY TABLE_NAME
Re: How to create a grand total from multiple SELECTs [message #203854 is a reply to message #203852] Thu, 16 November 2006 10:52 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Oops..misread the question. You can do this in SQL*Plus:

break on report
compute sum of x on report
SQL> select 1 as x from dual union select 5 as x from dual
X
----------
1
5
----------
6
Previous Topic: Format Time
Next Topic: Tuning SQL query
Goto Forum:
  


Current Time: Fri Dec 02 14:02:45 CST 2016

Total time taken to generate the page: 0.10130 seconds