Click here to Skip to main content
15,884,085 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
here is my sql :-


SQL
select ROW_NUMBER() OVER (ORDER BY t.studentid) AS SNo,max(t.roll_no) as RollNo,max(t.student) as Student ,
max([MST1(10)]) as [1   MST1(10)],max([MST2(20)]) as [2 MST2(20)]
From
( select  t.studentid,'' as roll_no,'' as student,'' as subject ,'' as course,t.sem ,
[1] as  [MST1(10)],[2] as  [MST2(20)]
from exammarksheet
pivot
(max (obtMarks) for ExamID in ([1],[2])) as t  where (t.subjectid = 2 And t.courseid = 1492 And t.sem = 3 And t.classesid = 17 And  t.sessionid = 20142015)
union all
select  distinct student.studentid, roll_no,student ,subjectall.code as subject,course.code as course,exammarksheet.sem , '' as a,'' as a,'' as a from student
inner join exammarksheet on student.studentid=exammarksheet.studentid
inner join course on student.courseid=course.courseid
inner join subjectall on exammarksheet.subjectid=subjectall.subjectid
where exammarksheet.subjectid = 2 And exammarksheet.courseid = 1492 And exammarksheet.sem = 3
and exammarksheet.sessionid=20142015 And exammarksheet.classesid = 17  ) as t   group by t.sem,t.studentid






i need sum of [MST1(10)] and [MST2(20)] as Total.

how can i do it.
Posted
Updated 28-Aug-14 20:34pm
Comments
syed shanu 29-Aug-14 1:51am    
So what is your problem,
select col1+col2 from yourtablename.

1 solution

Try this in your query where you want
SQL
([MST1(10)] + [MST2(20)]) as Total
 
Share this answer
 
Comments
TCS54321 29-Aug-14 2:00am    
Msg 8120, Level 16, State 1, Line 2
Column 't.MST1(10)' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 't.MST2(20)' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
TCS54321 29-Aug-14 2:03am    
my query:-




select ROW_NUMBER() OVER (ORDER BY t.studentid) AS SNo,max(t.roll_no) as RollNo,max(t.student) as Student ,
max([MST1(10)]) as [1 MST1(10)],max([MST2(20)]) as [2 MST2(20)], ([MST1(10)] + [MST2(20)]) as Total
From
( select t.studentid,'' as roll_no,'' as student,'' as subject ,'' as course,t.sem ,
[1] as [MST1(10)],[2] as [MST2(20)],'' as total
from exammarksheet
pivot
(max (obtMarks) for ExamID in ([1],[2])) as t where (t.subjectid = 2 And t.courseid = 1492 And t.sem = 3 And t.classesid = 17 And t.sessionid = 20142015)
union all
select distinct student.studentid, roll_no,student ,subjectall.code as subject,course.code as course,exammarksheet.sem , '' as a,'' as a,'' as a from student
inner join exammarksheet on student.studentid=exammarksheet.studentid
inner join course on student.courseid=course.courseid
inner join subjectall on exammarksheet.subjectid=subjectall.subjectid
where exammarksheet.subjectid = 2 And exammarksheet.courseid = 1492 And exammarksheet.sem = 3
and exammarksheet.sessionid=20142015 And exammarksheet.classesid = 17 ) as t group by t.sem,t.studentid,t.total
Vinay Mistry 29-Aug-14 2:54am    
without ([MST1(10)] + [MST2(20)]) as Total query working?
Vinay Mistry 29-Aug-14 2:56am    
Can't you use ([MST1(10)] + [MST2(20)]) as Total in subqueries?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900