Home » SQL & PL/SQL » SQL & PL/SQL » merging two queries to create a table (sql)
| merging two queries to create a table [message #635158] |
Mon, 23 March 2015 09:13  |
 |
palpali
Messages: 138
Registered: December 2014
Location: India
|
Senior Member |
|
|
Hallo all..
I would like to share my Problem with you all. 
well, I am trying to merge two different queries to create a table from the Output of These two queries.
my first question is:
1) How can i do this? is that correct where i have used UNION ALL? or is this any other methods that i can do?
Others:
1) the both tables have the same column Name
2) and in my final Output, i would like to have final sum of rate1 and rate2 from the both tables
i.e (test1.sum_rate1 + test12.sum_rate1) as Final_sum1
and (test1.sum_rate2 + test12.sum_rate2) as Final_sum2
can you please help me, how can i do this?
thanking you in advance
regards,
my test code:
create table test1 (
pid Number,
gid Number,
rate1 Number,
rate2 Number
);
insert into test1 (pid, gid, rate1, rate2) values (20,105,23,25);
insert into test1 (pid, gid, rate1, rate2) values (21,106,12,20);
insert into test1 (pid, gid, rate1, rate2) values (20,105,10,23);
insert into test1 (pid, gid, rate1, rate2) values (24,109,20,30);
insert into test1 (pid, gid, rate1, rate2) values (24,109,22,21);
commit;
create table test12 (
pid Number,
gid Number,
rate1 Number,
rate2 Number
);
insert into test12 (pid, gid, rate1, rate2) values (20,105,10,15);
insert into test12 (pid, gid, rate1, rate2) values (24,109,12,10);
insert into test12 (pid, gid, rate1, rate2) values (27,115,10,15);
insert into test12 (pid, gid, rate1, rate2) values (20,105,5,15);
insert into test12 (pid, gid, rate1, rate2) values (24,109,5,5);
commit;
create table test3 as (
select test1.pid, test1.gid, sum(test1.rate1)final_sum1, sum(test1.rate2)final_sum2 from test1 group by pid, gid
union all
select test12.pid, test12.gid, sum(test12.rate1)final_sum1, sum(test12.rate2)final_sum2 from test12 group by pid, gid
);
currently i have Output : (which gives me the sum from both table, but i would like to have just single sum per pid and gid)
PID GID Final_sum1 Final_sum2
20 105 33 48
21 106 12 20
24 109 42 51
27 115 10 15
20 105 15 30
24 109 17 15
|
|
|
|
|
|
|
|
|
|
| Re: merging two queries to create a table [message #635164 is a reply to message #635163] |
Mon, 23 March 2015 09:47  |
|
palpali
Messages: 138
Registered: December 2014
Location: India
|
Senior Member |
|
|
Well, i really don't understand what you mean.. :/ sorry..
but i wouild like to have the ouput of the two queries (from test1 and test12) into test3 (will be Input for test3) and the test3 should give me Output as follow: (as per your select Statement)
but my question is, how can i write this Statement in my following create table Statement to get same Output?
thanking you again
create table test3 as (
select test1.pid, test1.gid, sum(test1.rate1)final_sum1, sum(test1.rate2)final_sum2 from test1 group by pid, gid
union all
select test12.pid, test12.gid, sum(test12.rate1)final_sum1, sum(test12.rate2)final_sum2 from test12 group by pid, gid
);
and expected Output:
PID GID Sum(final_sum1) Sum(final_sum2)
27 115 10 15
20 105 48 78
21 106 12 20
24 109 59 66
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Aug 27 03:23:59 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
