Given a table, let's call it performance6a - with table structure >> student_id | math | history | language | science
I need to write a query which produces a report as below -
The group count is based on 2 categories, in this case, pass or fail and then subject.
Please note the operations are being performed atop system tables which can't be normalized or redesigned. Both categories can grow in numbers (category - pass/fail can increase up to 100 and category - the subject can increase up to ~5k)
What I have tried:
(SELECT 'PASS' AS STATUS, 'MATH', COUNT(ID) FROM PERFORMANCE6A WHERE MATH > 30) UNION
(SELECT 'FAIL' AS STATUS, 'MATH', COUNT(ID) FROM PERFORMANCE6A WHERE MATH <= 30) UNION
(SELECT 'PASS' AS STATUS, 'HISTORY', COUNT(ID) FROM PERFORMANCE6A WHERE HISTORY > 30) UNION
(SELECT 'FAIL' AS STATUS, 'HISTORY', COUNT(ID) FROM PERFORMANCE6A WHERE HISTORY <= 30) UNION
(SELECT 'PASS' AS STATUS, 'LANGUAGE', COUNT(ID) FROM PERFORMANCE6A WHERE LANGUAGE > 30) UNION
(SELECT 'FAIL' AS STATUS, 'LANGUAGE', COUNT(ID) FROM PERFORMANCE6A WHERE LANGUAGE <= 30) UNION
(SELECT 'PASS' AS STATUS, 'SCIENCE', COUNT(ID) FROM PERFORMANCE6A WHERE SCIENCE > 30) UNION
(SELECT 'FAIL' AS STATUS, 'SCIENCE', COUNT(ID) FROM PERFORMANCE6A WHERE SCIENCE <= 30);
This query gives me the right output, but the execution time exceeds 2s. I'm looking for ideas to optimize the query.
It might be faster if you unpivot and then aggregate:
select (case when score > 30 then 'PASS' else 'FAIL' end) as status, subject, count(*)
from ((select 'math' as subject, math as score from PERFORMANCE6A) union all
(select 'history' as subject, history as score from PERFORMANCE6A) union all
(select 'language' as subject, language as score from PERFORMANCE6A) union all
(select 'science' as subject, science as score from PERFORMANCE6A)
) ss
group by (case when score > 30 then 'PASS' else 'FAIL' end), subject