db2 query



IBM's flagship relational database management system

db2 query

Postby coolpinky » Thu Jun 27, 2013 7:04 pm

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
coolpinky
 
Posts: 11
Joined: Fri Jun 07, 2013 4:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: db2 query

Postby NicC » Thu Jun 27, 2013 11:46 pm

So what have you tried so far and what is wrong with your results?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: db2 query

Postby coolpinky » Fri Jun 28, 2013 9:16 am

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
coolpinky
 
Posts: 11
Joined: Fri Jun 07, 2013 4:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: db2 query

Postby coolpinky » Fri Jun 28, 2013 11:11 am

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.
coolpinky
 
Posts: 11
Joined: Fri Jun 07, 2013 4:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: db2 query

Postby coolpinky » Fri Jun 28, 2013 4:51 pm

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..
coolpinky
 
Posts: 11
Joined: Fri Jun 07, 2013 4:58 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post