Use a SQL query to retrieve the unique identifier for a specific subject and a specified score.

Question

Want to write a SQL query to get the unique IDs of those with a math score of 80, an English score of 70, and a total score of 400 or greater. The answer will be id-1 and 2

ID Subject Marks
1 Maths 80
1 English 70
1 Hindi 80
1 Science 80
1 SST 90
2 Maths 80
2 English 70
2 Hindi 90
2 Science 80
2 SST 100
3 Maths 80
3 English 60
3 Hindi 90
3 Science 100
3 SST 100

I'm confused on how to insert two topics together as a filter.

Valid query -

SELECT `ID` FROM `ff`
WHERE (`SUBJECT`='MATHS' AND `MARKS`= 80 AND `ID` IN (SELECT `ID` FROM `ff` GROUP BY `ID` HAVING SUM(`MARKS`) >=400) ) OR (` SUBJECT`= 'ENGLISH' AND `MARKS`=70 AND `ID` IN (SELECT `ID` FROM `ff` GROUP BY `ID` HAVING SUM(`MARKS`) >=400) )

But this does not give the expected results.

Answer 1

You can use conditional aggregation.

SELECT `ID` 
FROM `ff`
GROUP BY `ID` 
HAVING SUM(`MARKS`) >= 400  
  AND SUM(case when `SUBJECT` = 'English' then `MARKS`end) = 70 
  AND SUM(case when `SUBJECT` = 'Math' then `MARKS` end) = 80