|
View previous topic :: View next topic
|
| Author |
Message |
ksathishkumar83
New User
Joined: 21 Nov 2006
Posts: 50
Location: India
|
|
|
|
Hi,
i have student table as below
S_id subject marks
----- -------- -------
S1 T1 50
S1 T2 60
S1 T3 70
S1 T4 80
S1 T5 90
S2 T1 50
S2 T2 50
S2 T3 50
S3 T1 50
S3 T2 60
S3 T3 70
S3 T4 80
S3 T5 90
1. I want to sleect where the student has all the five subject marks in the table
In the above case i want to selct S1 and S3.
2. I want to display the student marks in horizontal order as below
S-iD Sub1 Sub2 Sub3 Sub4 Sub5 total
S1 50 60 70 80 90 350
S2 50 50 50 150
I searched the forum and i didn't fopund the solution. |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
1: join 5 times :
| Code: |
from tab1 A, tab1 B, tab1 C , tab1 D, tab1 E
where a.s_id = b.s_id and a.s_id = c.s_id and ...
and a.subject = 'T1'
and b.subject = 'T2'
... |
2:use CASE in combination with GROUP BY :
| Code: |
select s_ID
, sum(case when subject = 'T1' then marks else 0) as Sub1
, sum(case when subject = 'T2' then marks else 0) as Sub2
, sum(case when subject = 'T3' then marks else 0) as Sub3
, sum(case when subject = 'T4' then marks else 0) as Sub4
, sum(case when subject = 'T5' then marks else 0) as Sub5
, sum(marks) as total
from tab1
group by _ID |
|
|
| Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
1: i didn't run the query though.....let me know if there is anything wrong in the query. we can work with the query to get other info also (considering s_id and subject are unique)
| Code: |
SELECT S_ID
FROM TAB
GROUP BY S_ID
HAVING COUNT(*) > 5
|
|
|
| Back to top |
|
|
Ajay Baghel
Active User
.jpg)
Joined: 25 Apr 2007
Posts: 206
Location: Bangalore
|
|
|
|
| Nice use of case statement in second query. |
|
| Back to top |
|
|
|
|
