why im seeing this error..???
Msg 512, Level 16, State 1, Line 3Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
DECLARE @AdmissionNo VARCHAR(10)
;WITH cteStudentMarksDetails
AS (
select ts.AdmissionNo
,TestDate
,SubjectName
,MaxMarks
,Marks
,Round((Marks) * 100/ (MaxMarks),1) as SubjectPercentage
,DENSE_RANK() over(partition BY (SELECT MAX(Marks) FROM tblTestSystem TSS Where Tss.SubjectID = ts.SubjectID AND Tss.ClassID = ts.ClassID) order by ((Round((SELECT Marks FROM tblTestSystem TSS Where Tss.AdmissionNo = ts.AdmissionNo AND Tss.SubjectID = ts.SubjectID AND Tss.ClassID = ts.ClassID) * 100 / (SELECT MAX(Marks) FROM tblTestSystem TSS Where Tss.SubjectID = ts.SubjectID AND Tss.ClassID = ts.ClassID),1) )) DESC) AS SubjectPosition
,CASE WHEN Round((Marks) * 100/ (MaxMarks),1) >= 80 THEN 'A+'
WHEN Round((Marks) * 100/ (MaxMarks),1) >= 70 THEN 'A'
WHEN Round((Marks) * 100/ (MaxMarks),1) >= 60 THEN 'B'
WHEN Round((Marks) * 100/ (MaxMarks),1) >= 50 THEN 'C'
WHEN Round((Marks) * 100/ (MaxMarks),1) >= 40 THEN 'D'
WHEN Round((Marks) * 100/ (MaxMarks),1) >= 33 THEN 'E'
ELSE 'FAIL' END AS Grade,
CASE WHEN Round((Marks) * 100/ (MaxMarks),1) >= 80 THEN 'OutStanding'
WHEN Round((Marks) * 100/ (MaxMarks),1) >= 70 THEN 'Excellent'
WHEN Round((Marks) * 100/ (MaxMarks),1) >= 60 THEN 'Very Good'
WHEN Round((Marks) * 100/ (MaxMarks),1) >= 50 THEN 'Good'
WHEN Round((Marks) * 100/ (MaxMarks),1) >= 40 THEN 'Satisfactory'
WHEN Round((Marks) * 100/ (MaxMarks),1) >= 33 THEN 'Work Hard'
ELSE 'FAIL' END AS Remarks
,(Round((SELECT SUM(Marks) FROM tblTestSystem TSS WHERE Tss.AdmissionNo = ts.AdmissionNo) * 100/ (SELECT SUM(MaxMarks) FROM tblTestSystem TSS WHERE Tss.AdmissionNo = ts.AdmissionNo),1) ) TotalPercentage
,DENSE_RANK() OVER(ORDER BY ((ROUND((SELECT SUM(Marks) FROM tblTestSystem TSS WHERE Tss.AdmissionNo = ts.AdmissionNo) * 100 / (SELECT SUM(MaxMarks) FROM tblTestSystem TSS WHERE Tss.AdmissionNo = ts.AdmissionNo),1) )) DESC) AS ClassPosition
FROM tblTestSystem AS ts
INNER JOIN tblStdReg AS sr ON ts.AdmissionNo=sr.AdmissionNo
INNER JOIN tblDefClass AS dc ON ts.ClassID=dc.ClassID
INNER JOIN tblDefSection AS ds ON ts.SectionID=ds.SectionID
inner join tblAssignSubjects as si on si.ASID=ts.SubjectID
inner join tblDefSubject as des on si.SubjectID=des.SubjectID
)
SELECT * FROM cteStudentMarksDetails