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  |
 |
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
|
|
|
|
| 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  |
 |
Michel Cadot
Messages: 68641
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 #661104 is a reply to message #661102] |
Tue, 07 March 2017 15:32 |
|
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 #661161 is a reply to message #661105] |
Thu, 09 March 2017 07:16 |
Bill B
Messages: 1971
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); |
|
|
|
Goto Forum:
Current Time: Fri Apr 19 14:07:22 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
