Hi smile,
Refer below sample. For resolving your problem you can use sub query or use Inner Join.
SQL
DECLARE @TestDemo AS TABLE(AdmissionNo VARCHAR(20),Max INT, Mark INT)
INSERT INTO @TestDemo VALUES('R000001',150,111)
INSERT INTO @TestDemo VALUES('R000003',150,108)
INSERT INTO @TestDemo VALUES('R000007',150,108)
INSERT INTO @TestDemo VALUES('R000002',150,99)
INSERT INTO @TestDemo VALUES('R000011',150,99)
INSERT INTO @TestDemo VALUES('R000010',150,92)
INSERT INTO @TestDemo VALUES('R000006',150,91)
INSERT INTO @TestDemo VALUES('R000008',150,85)
INSERT INTO @TestDemo VALUES('R000012',150,84)
INSERT INTO @TestDemo VALUES('R000009',150,80)
INSERT INTO @TestDemo VALUES('R000005',150,77)
INSERT INTO @TestDemo VALUES('R000004',150,76)
INSERT INTO @TestDemo VALUES('R000013',150,67)
INSERT INTO @TestDemo VALUES('R000015',150,60)
INSERT INTO @TestDemo VALUES('R000014',150,49)
SELECT m.*
FROM @TestDemo b
INNER JOIN (
SELECT AdmissionNo,sum(Max) 'Total',sum(Mark) 'Obtain'
,dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS RANK
,Round(sum(Mark) * 100/ sum(Max),1) as Percentage
,(CASE WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'C'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'
ELSE 'FAIL' END ) AS Grade
,(CASE WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'
ELSE 'FAIL' END ) AS Remarks
,(CASE WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'His/Her performance has been outstanding.Keep it up.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Keep up the good work.All the best.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'He/She has the potential to do more.All the best.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'He/She has been doing good in studies.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'He/She needs to work hard in order to improve his/her grades.'
ELSE 'He/She is advised to pay attention towards studies.' END ) AS TRemarks
FROM @TestDemo
GROUP BY AdmissionNo) m ON b.AdmissionNo = m.AdmissionNo
WHERE b.AdmissionNo = 'R000012'
---------------------------
SELECT * FROM (
SELECT AdmissionNo,sum(Max) 'Total',sum(Mark) 'Obtain'
,dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS RANK
,Round(sum(Mark) * 100/ sum(Max),1) as Percentage
,(CASE WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'C'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'
ELSE 'FAIL' END ) AS Grade
,(CASE WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'
ELSE 'FAIL' END ) AS Remarks
,(CASE WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'His/Her performance has been outstanding.Keep it up.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Keep up the good work.All the best.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'He/She has the potential to do more.All the best.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'He/She has been doing good in studies.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'He/She needs to work hard in order to improve his/her grades.'
ELSE 'He/She is advised to pay attention towards studies.' END ) AS TRemarks
FROM @TestDemo
GROUP BY AdmissionNo) t
WHERE t.AdmissionNo = 'R000012'
Output
| AdmissionNo |
Total |
Obtain |
RANK |
Percentage |
Grade |
Remarks |
TRemarks |
| R000012 |
150 |
84 |
7 |
56 |
D |
Satisfactory |
He/She has been doing good in studies. |
