I have a SQL Query as below :
SELECT SUM(Z.MaxMarks) AS TotMaxMarks,
SUM(Z.MinMarks) AS TotMinMarks,
SUM(Z.SecuredMarks) AS TotSecuredMarks,
Z.IsPass
FROM (
SELECT Y.*
FROM (
SELECT X.MaxMarks,
X.SecuredMarks,
(X.MaxMarks * X.PassPercentage / 100) AS MinMarks,
CASE WHEN ((X.MaxMarks * X.PassPercentage / 100) < X.SecuredMarks) THEN 'True' ELSE 'False' END AS IsPass,
row_number() OVER (
PARTITION BY ChapterID ORDER BY UpdatedDate DESC
) AS SequencNo
FROM (
SELECT C.ChapterID,
T.MinimumMarks AS PassPercentage,
(
SELECT Sum(Q.MaximumMarks)
FROM MstQuestion Q,
TransStudentAnswer SA
WHERE Q.QuestionID = SA.QuestionID AND SA.TestAllotmentID = TA.TestAllotmentID
) AS MaxMarks,
(
SELECT Sum(Score)
FROM TransStudentAnswer
WHERE TestAllotmentID = TA.TestAllotmentID
) AS SecuredMarks,
TA.UpdatedDate
FROM TransTestAllotment TA,
MstStudent S,
MstBatchDetails B,
MstTest T,
MstChapter C
WHERE TA.StudentID = 47 AND TA.BatchID = 10 AND T.TestID = TA.TestID AND S.StudentID = TA.StudentID AND B.BatchID = TA.BatchID AND T.ChapterID = C.ChapterID AND TA.IsAttempted = 'True' AND TA.IsEvaluated = 'True'
) X
) Y
WHERE SequencNo = 1
) Z
GROUP BY Z.IsPass
It is returning results as below :
+-------------+-------------+-----------------+--------+
| TotMaxMarks | TotMinMarks | TotSecuredMarks | IsPass |
+-------------+-------------+-----------------+--------+
| 20 | 10 | 3 | False |
| 30 | 15 | 28 | True |
+-------------+-------------+-----------------+--------+
Is it possible to modify this query such that it returns one record with SUM(Z.MaxMarks),SUM(Z.MinMarks),SUM(Z.SecuredMarks) and TempIsPass column whose value will be true if value of the column Z.Ispass is true in all records of Z, else it should be false.
Aucun commentaire:
Enregistrer un commentaire