Using if else block in pivot query
I have a table
StudentID StudentName Subject Marks
1 Savita EC1 50
1 Savita EC2 55
1 Savita EC3 45
1 Savita EC4 34
1 Savita EC5 23
2 Rajesh EC1 34
2 Rajesh EC2 56
2 Rajesh EC3 12
2 Rajesh EC4 45
2 Rajesh EC5 23
3 Smita EC1 76
3 Smita EC2 45
3 Smita EC3 67
3 Smita EC4 56
3 Smita EC5 76
4 Rahul EC1 66
4 Rahul EC2 34
4 Rahul EC3 22
4 Rahul EC4 18
4 Rahul EC5 33
I wrote a query like
SELECT StudentName, EC1,EC2,EC3,EC4,EC5,TotalMarks, case
when EC1<30 and ec2<30 then 'fail'
when EC1<30 and EC3<30 then 'fail'
when EC1<30 and EC4<30 then 'fail'
when EC1<30 and EC5<30 then 'fail'
when EC2<30 and EC3<30 then 'fail'
when EC2<30 and EC4<30 then 'fail'
when EC2<30 and EC5<30 then 'fail'
when EC3<30 and EC4<30 then 'fail'
when EC3<30 and EC5<30 then 'fail'
when EC4<30 and EC5<30 then 'fail'
else 'pass'
end as Result
FROM (SELECT StudentName, EC1, EC2, EC3, EC4, EC5, TotalMarks=EC1+EC2+EC3+EC4+EC5
FROM Student
PIVOT(sum(Marks) for subject in([EC1],[EC2],[EC3],[EC4],[EC5],[TotalMarks]))as pivotTable) A
What gives the result of students who have less than 30 marks in two subjects as failing or otherwise
Rahul 66 34 22 18 33 173 fail
Rajesh 34 56 12 45 23 170 fail
Savita 50 55 45 34 23 207 pass
Smita 76 45 67 56 76 320 pass
I want to add 7 tags to every subject less than 30 and see students with an error after adding 7 tags. for example - after adding 7 labels, the rajesh entry should look like
Rajesh 34 56 19 45 30 170 pass
source to share
You can try this
SELECT
StudentName,
CASE WHEN EC1 < 30 THEN EC1 + 7 ELSE EC1 END AS EC1,
CASE WHEN EC2 < 30 THEN EC2 + 7 ELSE EC2 END AS EC2,
CASE WHEN EC3 < 30 THEN EC3 + 7 ELSE EC3 END AS EC3,
CASE WHEN EC4 < 30 THEN EC4 + 7 ELSE EC4 END AS EC4,
CASE WHEN EC5 < 30 THEN EC5 + 7 ELSE EC5 END AS EC5,
Total = (EC1 + EC2 + EC3 + EC4 + EC5),
CASE
WHEN EC1 < 23 AND EC2 < 23 THEN 'FAIL'
WHEN EC1 < 23 AND EC3 < 23 THEN 'FAIL'
WHEN EC1 < 23 AND EC4 < 23 THEN 'FAIL'
WHEN EC1 < 23 AND EC5 < 23 THEN 'FAIL'
WHEN EC2 < 23 AND EC3 < 23 THEN 'FAIL'
WHEN EC2 < 23 AND EC4 < 23 THEN 'FAIL'
WHEN EC2 < 23 AND EC5 < 23 THEN 'FAIL'
WHEN EC3 < 23 AND EC4 < 23 THEN 'FAIL'
WHEN EC3 < 23 AND EC5 < 23 THEN 'FAIL'
WHEN EC4 < 23 AND EC5 < 23 THEN 'FAIL'
ELSE 'PASS'
END AS Result
FROM
(
SELECT * FROM Student
) AS ST
PIVOT
(
SUM(Marks) For [Subject] IN (EC1, EC2, EC3, EC4, EC5)
) AS PV
Output
Rahul 66 34 29 25 33 173 FAIL
Rajesh 34 56 19 45 30 170 PASS
Savita 50 55 45 34 30 207 PASS
Smita 76 45 67 56 76 320 PASS
source to share
Perhaps this is what you are looking for:
SELECT A.StudentName, EC1,EC2,EC3,EC4,EC5,Total,
case when fail2 >= 2 then 'Failure'
when fail >= 2 then 'Near Pass'
else 'Pass' end as Result
FROM
( SELECT StudentName, EC1, EC2, EC3, EC4, EC5
FROM Student
PIVOT(sum(Marks) for subject in([EC1],[EC2],[EC3],[EC4],[EC5]))as pt) A,
( select
studentName,
sum(case when Marks < 30 then 1 else 0 end) as fail,
sum(case when Marks < 23 then 1 else 0 end) as fail2,
sum(case when Marks >= 30 then 1 else 0 end) as pass,
sum(marks) as total
from
student
group by
studentname
) B
where
A.StudentName = B.StudentName
I removed the comparison logic that was for all combinations of failures and replaced it with the + group + case sum from the original table so that you can quantify the number of errors for failures, skips and passes for each student without having to list all cases separately.
You can check this in SQL Fiddle
source to share
You can use the windows version of the aggregation functions SUM
, COUNT
to simplify your query:
SELECT StudentID, StudentName,
[EC1],[EC2],[EC3],[EC4],[EC5],
Total,
CASE WHEN Below30 >= 2 THEN 'Fail'
ELSE 'Pass'
END AS WithoutBonus,
CASE WHEN Below23 >= 2 THEN 'Fail'
ELSE 'Pass'
END AS WithBonus
FROM (SELECT StudentID, StudentName, Subject, Marks,
SUM(Marks) OVER (PARTITION BY StudentID) AS Total,
COUNT(CASE WHEN Marks < 30 THEN 1 END)
OVER (PARTITION BY StudentID) AS Below30,
COUNT(CASE WHEN Marks < 23 THEN 1 END)
OVER (PARTITION BY StudentID) AS Below23
FROM mytable ) AS src
PIVOT (
MAX (Marks)
FOR Subject IN ([EC1],[EC2],[EC3],[EC4],[EC5]) ) pvt
ORDER BY StudentName
Below30
counts the number of subjects having a sign that is <30 per StudentID
. Below23
counts the number of subjects having a sign that is <23 per StudentID
.
Therefore, WithoutBonus
is 'Fail'
, if a particular student has 2 or more subjects with Mark<30
, whereas WithBonus
is 'Fail'
, if a particular student has 2 or more people, subjects with Mark<23
.
If you want to output the values Marks
increased by 7 in case they are < 30
, and you don't need a score WithoutBonus
, you can use the following version:
SELECT StudentID, StudentName,
[EC1],[EC2],[EC3],[EC4],[EC5],
Total,
CASE WHEN Below23 >= 2 THEN 'Fail'
ELSE 'Pass'
END AS WithBonus
FROM (SELECT StudentID, StudentName, Subject,
CASE WHEN Marks < 30 THEN Marks+7 ELSE Marks END AS Marks,
SUM(Marks) OVER (PARTITION BY StudentID) AS Total,
COUNT(CASE WHEN Marks < 23 THEN 1 END) OVER (PARTITION BY StudentID) AS Below23
FROM mytable ) AS src
PIVOT (
MAX (Marks)
FOR Subject IN ([EC1],[EC2],[EC3],[EC4],[EC5]) ) pvt
ORDER BY StudentName
Output:
StudentID StudentName EC1 EC2 EC3 EC4 EC5 Total WithBonus
--------------------------------------------------------------
4 Rahul 66 34 29 25 33 173 Fail
2 Rajesh 34 56 19 45 30 170 Pass
1 Savita 50 55 45 34 30 207 Pass
3 Smita 76 45 67 56 76 320 Pass
source to share