Home » SQL & PL/SQL » SQL & PL/SQL » How do I total each column of data with multiple queries? (Oracle 12.1, Version 3.2.20.09)
How do I total each column of data with multiple queries? [message #661064] Mon, 06 March 2017 11:05 Go to next message
Dallas Graves
Messages: 4
Registered: March 2017
Junior Member
Hello All,

My experience with Oracle is very limited with that being said, I am trying to sum multiple columns of data to give a total at the bottom of "each" column. I have tried the Rollup function with no success but I have no errors either.

Here is the actual query that I am trying to sum up each column of data(not a total of all columns but a total for "each" column)....there is a total of 8 GROUP BY functions:

With
  Query1 As
  (
    Select Distinct
      C1 As Org,
      Sum(C2) "A_P_Fte"
    
    From
      T1
    Inner Join
      T2
    On
      (T1.Billet_Nbr = T2.Billet_Nbr 
    And
      T1.Pos_Nbr = T2.Pos_Nbr)
    Where
      T1.Pos_Typ = 'P'    
    And
      T2.Stat_Cd = 'A'   
    And
      T1.Fund = '199'     
    Group By
      Rollup(Org)
    ),
  
  Query2 as
  (
    Select Distinct
      C1 As Org,
      Sum(C3) "A_P_Fte_Salary"
      
    From
      T1
    Inner Join
      T2
    On
      (T1.Billet_Nbr = T2.Billet_Nbr 
    And
      T1.Pos_Nbr = T2.Pos_Nbr)
    Where
      T1.Pos_Typ = 'P'    
    And
      T2.Stat_Cd = 'A'   
    And
      T1.Fund = '199'    
    Group By
      Rollup(Org)
    ),
  
  Query3 as
  (
    Select Distinct
      C1 As Org,
      Sum(C2) "V_P_Fte"
    
      
    From
      T1
    Inner Join
      T2
    On
      (T1.Billet_Nbr = T2.Billet_Nbr 
    And
      T1.Pos_Nbr = T2.Pos_Nbr)
    Where
      T1.Pos_Typ = 'P'  
    And
      T2.Stat_Cd = 'V'   
    And
      T1.Fund = '199'        
    Group By
      Rollup(Org)
    ),
  
  Query4 as
  (
    Select Distinct
      C1 As Org,
      Sum(C3) "V_P_Fte_Salary"
      
    From
      T1
    Inner Join
      T2
    On
      (T1.Billet_Nbr = T2.Billet_Nbr 
    And
      T1.Pos_Nbr = T2.Pos_Nbr)
    Where
      T1.Pos_Typ = 'P'  
    And
      T2.Stat_Cd = 'V' 
    And
      T1.Fund = '199'     
    Group By
      Rollup(Org)
    ),
  
  Query5 As
  (
    Select Distinct
      C1 As Org,
      Sum(C2) "A_S_Fte" 
      
    From
      T1
    Inner Join
      T2
    On
      (T1.Billet_Nbr = T2.Billet_Nbr 
    And
      T1.Pos_Nbr = T2.Pos_Nbr)
    Where
      T1.Pos_Typ = 'S'    
    And
      T2.Stat_Cd = 'A'   
    And
      T1.Fund = '199'       
    Group By
      Rollup(Org)
    ),
  
  Query6 as
  (
    Select Distinct
      C1 As Org,
      Sum(C3) "A_S_Fte_Salary"
      
    From
      T1
    Inner Join
      T2
    On
      (T1.Billet_Nbr = T2.Billet_Nbr 
    And
      T1.Pos_Nbr = T2.Pos_Nbr)
    Where
      T1.Pos_Typ = 'S'    
    And
      T2.Stat_Cd = 'A'  
    And
      T1.Fund = '199'    
    Group By
      Rollup(Org)
    ),
  
  Query7 as
  (
    Select Distinct
      C1 As Org,
      Sum(C2) "V_S_Fte"
      
    From
      T1
    Inner Join
      T2
    On
      (T1.Billet_Nbr = T2.Billet_Nbr 
    And
      T1.Pos_Nbr = T2.Pos_Nbr)
    Where
      T1.Pos_Typ = 'S'    
    And
      T2.Stat_Cd = 'V'   
    And
      T1.Fund = '199'     
    Group By
      Rollup(Org)
    ),
  
  Query8 as
  (
    Select Distinct
      C1 As Org,
      Sum(C3) "V_S_Fte_Salary"
      
    From
      T1
    Inner Join
      T2
    On
      (T1.Billet_Nbr = T2.Billet_Nbr 
    And
      T1.Pos_Nbr = T2.Pos_Nbr)
    Where
      T1.Pos_Typ = 'S'    
    And
      T2.Stat_Cd = 'V'   
    And
      T1.Fund = '199'       
    Group By
      Rollup(Org)
    )

Select Distinct
  C1 As Org,

  Case When Query1."A_P_Fte" Is Null 
	Then 0 
	Else Query1."A_P_Fte" 
    End As "A_P_Fte",
  
  Case When Query2."A_P_Fte_Salary" Is Null 
	Then 0 
	Else Query2."A_P_Fte_Salary" 
    End As "A_P_Fte_Salary",
  
  Case When Query3."V_P_Fte" Is Null 
	Then 0 
	Else Query3."V_P_Fte" 
    End As "V_P_Fte",
  
  Case When Query4."V_P_Fte_Salary" Is Null 
	Then 0 
	Else Query4."V_P_Fte_Salary" 
    End As "V_P_Fte_Salary",
  
  Case When Query5."A_S_Fte" Is Null 
	Then 0 
	Else Query5."A_S_Fte" 
    End As "A_S_Fte",
  
  Case When Query6."A_S_Fte_Salary" Is Null 
	Then 0 
	Else Query6."A_S_Fte_Salary" 
    End As "A_S_Fte_Salary",
  
  Case When Query7."V_S_Fte" Is Null 
	Then 0 
	Else Query7."V_S_Fte" 
    End As "V_S_Fte",
  
  Case When Query8."V_S_Fte_Salary" Is Null 
	Then 0 
	Else Query8."V_S_Fte_Salary" 
    End As "V_S_Fte_Salary"
  
From
  T1
Inner Join
  T2
On
  (T1.Billet_Nbr = T2.Billet_Nbr 
And
  T1.Pos_Nbr = T2.Pos_Nbr)
Left Outer Join
  Query1
On
  C1 = Query1.Org
Left Outer Join
  Query2
On
  C1 = Query2.Org
Left Outer Join
  Query3
On
  C1 = Query3.Org 
Left Outer Join
  Query4
On
  C1 = Query4.Org
Left Outer Join
  Query5
On
  C1 = Query5.Org
Left Outer Join
  Query6
On
  C1 = Query6.Org
Left Outer Join
  Query7
On
  C1 = Query7.Org 
Left Outer Join
  Query8
On
  C1 = Query8.Org
Where
  (C1 != '121' 
  And C1 != '809' 
  And C1 != '817' 
  And C1 != '827' 
  And C1 != '925' 
  And C1 != '933' 
  And C1 != '974' 
  And C1 != '975' 
  And C1 != '976')
Order By
  Org;

Here is the sample data for the query above:
ORG    A_P_Fte A_P_Fte_Salary    V_P_Fte V_P_Fte_Salary    A_S_Fte A_S_Fte_Salary    V_S_Fte V_S_Fte_Salary
--- ---------- -------------- ---------- -------------- ---------- -------------- ---------- --------------
002     181.14     8951685.99        1.5       50090.97      198.5      655910.35         65      130452.84 
003     166.22      8286207.6       9.36      435760.33        182      630072.96         31       51575.15 
004       33.7     1602631.15          1       51750.38         18       31654.83          3        4577.52 
006       15.5      872221.57          1       52808.49         17       26299.99         13           6300 
030      22.36     1201375.53          1       15101.92         24       26599.69          1           1000 
041      67.17     3378251.61          3       98767.23         63      169596.03         10       29063.08 
042      61.82     3108789.35          2      105800.86         50       142548.6          8          17500
...     ......     ..........        ....     .........       .....     .........         ..       ........
973         19     1200935.81          1        98423.8          2        1566.63          2        1366.63 
977        1.2       64871.07          1       25015.76          0              0          0              0 
997        0.2       27224.35          0              0          0              0          0              0 
999          5      209655.95          7      392783.25          1         3077.5          7          15500 

 89 rows selected           


The Rollup function did not sum up each column for me.

Here is an example of a smaller query that sums up "each" column using the Rollup function.....there is only one GROUP BY function in the query:

Select Distinct
  C1 As Org,
  Sum(C2 ) As Fte_Counts,
  Sum(C3 ) As Fte_Salary_Per_Org
  
From
  T1
Inner Join
  T2
On
  (T1.Billet_Nbr = T2.Billet_Nbr 
And
  T1.Pos_Nbr = T2.Pos_Nbr)
Where
  T1.Pos_Typ = 'P'
And
  (C1 != '121'   
  And C1 != '809'
  And C1 != '817'
  And C1 != '827'
  And C1 != '925'
  And C1 != '933' 
  And C1 != '974'
  And C1 != '975'
  And C1 != '976')
  
And
  T2.Stat_Cd = 'A' 

And
  T1.Fund = '199' 
  
Group By
  Rollup(Org);     -- sums the column data

Here is the sample data for the query above(notice the last row is the sum for that column except for the first column...the first column is the primary key):
ORG FTE_COUNTS FTE_SALARY_PER_ORG
--- ---------- ------------------
002     181.14         8951685.99 
003     166.22          8286207.6 
004       33.7         1602631.15 
006       15.5          872221.57 
030      22.36         1201375.53 
041      67.17         3378251.61 
042      61.82         3108789.35
...      .....         ..........
977        1.2           64871.07 
997        0.2           27224.35 
999          5          209655.95 
       2565.08        123157939.4 

 89 rows selected
Any help will be greatly appreciated and thanks in advance.
Re: How do I total each column of data with multiple queries? [message #661065 is a reply to message #661064] Mon, 06 March 2017 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

You don't get the total because your base tables have no row to match with your totals, you must add a row to these ones:
Here's an example of what you can do:
SQL> with
  2    owners as (
  3      select distinct owner from dba_objects union all select '....' from dual
  4    ),
  5    tables as (
  6      select decode(grouping(owner),1,'....',owner) owner , count(*) nb
  7      from dba_tables group by rollup(owner)
  8    ),
  9    views as (
 10      select decode(grouping(owner),1,'....',owner) owner, count(*) nb
 11      from dba_views group by rollup(owner)),
 12    syns as (
 13      select decode(grouping(owner),1,'....',owner) owner, count(*) nb
 14      from dba_synonyms group by rollup(owner)
 15    )
 16  select nullif(owners.owner,'....') owner,
 17         nvl(tables.nb,0) tables,
 18         nvl(views.nb,0) views,
 19         nvl(syns.nb,0) synonyms
 20  from owners
 21       left outer join tables on tables.owner = owners.owner
 22       left outer join views on views.owner = owners.owner
 23       left outer join syns on syns.owner = owners.owner
 24  order by owner nulls last
 25  /
OWNER                              TABLES      VIEWS   SYNONYMS
------------------------------ ---------- ---------- ----------
APEX_030200                           360        125         45
APPQOSSYS                               4          0          1
BI                                      0          0          8
CTXSYS                                 50         77          0
DBSNMP                                 20          7          1
EXFSYS                                 47         57          0
FLOWS_FILES                             1          0          5
HR                                      7          1          0
IX                                     17          8          0
MC_RMAN                                 9          0          0
MDSYS                                 123         86          0
MESDVD$LECTEUR                          0          0         15
MESDVD$PROPRIO                         15          0          0
MESDVD$TOUT                             0          0         15
MICHEL                                262         16          7
OE                                     10         13          6
OLAPSYS                               126        307          0
ORACLE_OCM                              0          0          0
ORDDATA                                73         25          0
ORDPLUGINS                              0          0          0
ORDSYS                                  5          5          0
OUTLN                                   3          0          0
PERFSTAT                               72          1          0
PM                                      2          0          0
PUBLIC                                  0          0      34030
SCOTT                                   8          0          0
SH                                     17          1          0
SI_INFORMTN_SCHEMA                      0          0          8
SYS                                  1024       3946          9
SYSMAN                                728        471          0
SYSTEM                                167         14          8
T_USER                                  0          0          0
WATCHER                                 5          0          0
WMSYS                                  44        112          0
XDB                                    32          5          0
                                     3231       5277      34158
Re: How do I total each column of data with multiple queries? [message #661094 is a reply to message #661065] Tue, 07 March 2017 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: How do I total each column of data with multiple queries? [message #661101 is a reply to message #661094] Tue, 07 March 2017 14:43 Go to previous messageGo to next message
Dallas Graves
Messages: 4
Registered: March 2017
Junior Member
Michel,

Thanks alot for the fast response. Sorry to get back so late. If I were to create this table it would look something like this( I do not have permissions to create nor insert, only view):

CREATE TABLE "T1" 
   ("C1" CHAR(3 BYTE) DEFAULT ' ' NOT NULL ENABLE,  
   "C2" NUMBER(7,5)   DEFAULT  0  NOT NULL ENABLE, 
   "C3" NUMBER(9,2)   DEFAULT  0  NOT NULL ENABLE
   CONSTRAINT T1_pk   PRIMARY KEY ("C1"))

I hope this will provide the create and constraint data you have asked for. The above example worked great for counting records in another report I have(happy accident) but I am needing to know the total "amount" for each column and not total records for each column. If you don't mind, can you explain what the "nb" means in "count(*) nb"?
Re: How do I total each column of data with multiple queries? [message #661102 is a reply to message #661101] Tue, 07 March 2017 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know what you mean by "amount" your table has only C1, C2, C3 columns. COUNT is just an example, use SUM if it is what you need in your actual case.
You don't need any privilege to write statements, just your fingers. I you provide a test case I can execute then I can show you with your tables and data, otherwise I can just show with what I have: Oracle dictionary data.

Re: How do I total each column of data with multiple queries? [message #661103 is a reply to message #661101] Tue, 07 March 2017 15:14 Go to previous messageGo to next message
joy_division
Messages: 4902
Registered: February 2005
Location: East Coast USA
Senior Member
Dallas Graves wrote on Tue, 07 March 2017 15:43
If you don't mind, can you explain what the "nb" means in "count(*) nb"?
'nb' is just an alias for count(*) in Michel's example so that it can be used in the query.
Re: How do I total each column of data with multiple queries? [message #661104 is a reply to message #661102] Tue, 07 March 2017 15:32 Go to previous messageGo to next message
Dallas Graves
Messages: 4
Registered: March 2017
Junior Member
To clarify with what I was meaning by "amount":
INSERT INTO T1 (C1, C2, C3) VALUES ('002', '1', '2000');
INSERT INTO T1 (C1, C2, C3) VALUES ('002', '5', '100');
INSERT INTO T1 (C1, C2, C3) VALUES ('002', '.2', '33000');
INSERT INTO T1 (C1, C2, C3) VALUES ('002', '1.8','200');

The "amount" for C2 would be '8' and C3 would be '35300'.
I don't know why I didn't try replacing count with sum but this works very nicely. Thank you very much for the help.
Re: How do I total each column of data with multiple queries? [message #661105 is a reply to message #661103] Tue, 07 March 2017 15:34 Go to previous messageGo to next message
Dallas Graves
Messages: 4
Registered: March 2017
Junior Member
That makes sense now. Thanks Joy.
Re: How do I total each column of data with multiple queries? [message #661161 is a reply to message #661105] Thu, 09 March 2017 07:16 Go to previous message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
do not insert into a number column by making a number a string. You are forcing conversion and that should be avoided if you can. Your insert statements should be

INSERT INTO T1 (C1, C2, C3) VALUES ('002', 1, 2000);
INSERT INTO T1 (C1, C2, C3) VALUES ('002', 5, 100);
INSERT INTO T1 (C1, C2, C3) VALUES ('002', .2, 33000);
INSERT INTO T1 (C1, C2, C3) VALUES ('002', 1.8,200);
Previous Topic: which is best to locate row in oracle table
Next Topic: FOR UPDATE NOWAIT and ORA-29285: file write error
Goto Forum:
  


Current Time: Sat Oct 20 04:15:19 CDT 2018