i have created tables item and item2, I know maybe it's data redundancy but i want to know how can select it, and create a view?
create table item(
id number(10) primary key,
name varchar2(20),
mark number(10));
insert into item values(10,'Apple1',23);
insert into item values(11,'Apple2',0);
insert into item values(12,'Apple3',0);
insert into item values(13,'Apple4',0);
insert into item values(14,'Apple4',0);
insert into item values(15,'Apple4',0);
insert into item values(16,'Apple4',0);
create table item2(
id number(10),
mark number(10));
alter table item2 add(constraint id_fk FOREIGN KEY (id) references item(id));
Insert into item2 values(10,1);
Insert into item2 values(10,1);
Insert into item2 values(11,7);
Insert into item2 values(12,14);
I can query both:
select * from item;
ID Name Mark
10 Apple1 23
11 Apple2 0
12 Apple3 0
13 Apple4 0
14 Apple4 0
15 Apple4 0
16 Apple4 0
select * from item2;
ID Mark
10 1
10 1
11 7
12 14
I want to get the result set below using the select statement sum from the item and item2 tables:
ID Name Mark
10 Apple1 25
11 Apple2 7
12 Apple3 14
13 Apple4 0
14 Apple4 0
15 Apple4 0
16 Apple4 0
How can I combine my queries to produce that output?
If I understand this correctly, you want to "pretend" that the second table had the NAME column also, populated according to the first table; then you would want to GROUP BY id and get the sum of MARK.
If so, instead of joining the tables to get the names (either before or after combining the tables and computing the sums), you can use a UNION ALL, in which you insert a fake NAME column with NULL in it for the second table; then you group by id, you sum the MARK column, and you take the MAX over NAME. MAX ignores NULL, so it will just pick the name from table ITEM.
The solution below follows that logic in every detail.
select id, max(name) as name, sum(mark) as mark
from ( select id, name, mark
from item
union all
select id, null as name, mark
from item2
)
group by id
;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments