Page 1 of 1

db2 query

PostPosted: Thu Jun 27, 2013 7:04 pm
by coolpinky
Hi..

I have the following query .I have 3 tables..Need the result in table 4 as..

Table a


Teacher Students
Mohan pankaj
Mohan sudeepa
Mohan sujitra
Mohan Sumit


Table b (semester 1 marks)
Pankaj Maths 50
Pankaj English 40
Pankaj Physics 60
Sudeepa Chemisty 40
Sujitra Physic 10
Sujitra English 40
Sujitra Hindi 70

Table c (semester 2 marks)

Pankaj Chemistry 50
Pankaj English 40
Pankaj Physics 60
Sudeepa Science 40
Sujitra Physic 10
Sujitra Maths 40
Sujitra Hindi 70


O/p Result table

Maths 90
English 80
Physics 140
Chemistry 110
Hindi 140
Science 40


Result table is summation of all subjects...with all subjects name..Can anyone suggest any query for the same

Re: db2 query

PostPosted: Thu Jun 27, 2013 11:46 pm
by NicC
So what have you tried so far and what is wrong with your results?

Re: db2 query

PostPosted: Fri Jun 28, 2013 9:16 am
by coolpinky
I tried the following query..

Select subject,sum(marks) from
( select students,marks
from table b

where ( select subject from table a where teacher=in-taecher)

union

select students,makes
from table c

where (select subject from table where teacher=in-teahcer)

group by subject


I cant test as I do not have Db2

Re: db2 query

PostPosted: Fri Jun 28, 2013 11:11 am
by coolpinky
I wrote query as

select subject,sum(marks) from sample.b where
student in ( select student from sample.a where teacher='mohan')
group by subject

union all
select subject,sum(marks) from sample.c
where student in ( select student from sample.test where teacher='mohan')
group by subject

I am getting output as

hindi 70.00
maths 40.00
physics 10.00
science 40.00
chemistry 40.00
english 40.00
hindi 70.00
physics 10.00

Hindi Marks are not adding up.I want my output as
hindi 140.00
maths 40.00
physics 10.00
science 40.00
chemistry 40.00
english 40.00
physics 10.00

can you guide me on how to add up same value for 2 tables.

Re: db2 query

PostPosted: Fri Jun 28, 2013 4:51 pm
by coolpinky
Soory I want my putput as

Subject marks of sem1 marks of sem2
chemistry (aggregate of all students marks) ( aggregate of all student marks)
Hindi -- --

and so on..