Tuesday, 6 August 2013

Issue in the SQL Server query

Issue in the SQL Server query

I have a table contains Mark entry. Here I want to prepare progress sheet
of a particular student.So that the marks entered in the table according
to test. test 1 contains all marks of subjects ie, english,Botony etc.
test 2 also contains the marks of subject
For that I Used the query
union all is based on testid
select distinct W.SubjectName,W.StudentId, W.Test1,W.Test2
from(
select distinct SB.SubjectName,ME.StudentId, ME.Mark as Test1,0 as Test2
from MarkEntry ME inner join Subject SB on SB.SubjectId=ME.SubjectId
where ME.TestId=1 and ME.GradeId=5 and ME.SectionId=9 and ME.TermId=1
and ME.LevelId=1 and ME.StreamId=2 and ME.AcYear=14
group by ME.Mark,ME.StudentId,SB.SubjectName
union all
select distinct SB.SubjectName,ME.StudentId, 0 as Test1,ME.Mark as Test2
from MarkEntry ME
inner join Subject_DT SB on SB.SubjectId=ME.SubjectId
where ME.TestId=2 and ME.GradeId=5 and ME.SectionId=9 and ME.TermId=1 and
ME.LevelId=1 and
ME.StreamId=2 and ME.AcYear=14
group by ME.Mark,ME.StudentId,SB.SubjectName)W where W.StudentId=1052
group by
W.StudentId,W.Test1,W.Test2,W.SubjectName
My result is like this:
SubjectName StudentId Test1 Test2
English 1052 0.0 23.0
Botony 1052 0.0 32.0
Zoology 1052 0.0 32.0
Botony 1052 10.0 0.0
English 1052 10.0 0.0
Zoology 1052 20.0 0.0
But I need it like this
SubjectName StudentId Test1 Test2
English 1052 10.0 23.0
Botony 1052 10.0 32.0
Zoology 1052 20.0 32.0
Anybody have suggestions?

No comments:

Post a Comment