Mysql – How to place “WHERE CLAUSE ” to this query in order to filter a results

MySQLpivot

This is mysql query that found on the internet and modified to my needs, but I failed to place where clause in order to filter result.

SELECT 
stid, 
MAX( CASE WHEN courseid = 'MATH' THEN marks END) MATH ,
MAX( CASE WHEN courseid = 'HIST' THEN marks END) HIST,
MAX( CASE WHEN courseid = 'SCI' THEN marks END) SCI,
MAX( CASE WHEN courseid = 'GEO' THEN marks END) GEO,
MAX( CASE WHEN courseid = 'ENG' THEN marks END) ENG,
MAX( CASE WHEN courseid = 'CIV' THEN marks END) CIV,
MAX( CASE WHEN courseid = 'VS' THEN marks END) VS, 
MAX( CASE WHEN courseid = 'KISW' THEN marks END) KISW 
FROM finalresults 
GROUP BY stid ORDER BY 1"

Best Answer

Between FROM and GROUP BY.

SELECT 
stid, 
MAX( CASE WHEN courseid = 'MATH' THEN marks END) MATH ,
MAX( CASE WHEN courseid = 'HIST' THEN marks END) HIST,
MAX( CASE WHEN courseid = 'SCI' THEN marks END) SCI,
MAX( CASE WHEN courseid = 'GEO' THEN marks END) GEO,
MAX( CASE WHEN courseid = 'ENG' THEN marks END) ENG,
MAX( CASE WHEN courseid = 'CIV' THEN marks END) CIV,
MAX( CASE WHEN courseid = 'VS' THEN marks END) VS, 
MAX( CASE WHEN courseid = 'KISW' THEN marks END) KISW 
FROM finalresults 
WHERE field=value
GROUP BY stid
ORDER BY 1
Related Question