Question 221509 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL query to get unique id having specific marks in specific subject

Wanted to write a SQL query to get unique ids which has 80 marks in Maths and 70 Marks in English and Total Marks is >= 400. Ans would 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 am confused how I will insert both subjects as filter together.

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 is not giving expected result.

Tags:

2

Answers


  1. 0

    Since you need the unique id’s you need to group by the column id. Then WHERE clause meets condition of needed grades for Maths and English corresponding grades. Last, using keyword "having" for the aggregated function sum() that is needed for the sum of all the marks of each id the below query is created:

    SELECT ID
FROM ff
WHERE 
    (Subject = 'Maths' AND Marks = 80) OR (Subject = 'English' AND Marks = 70)
GROUP BY ID
HAVING SUM(Marks) >= 400
    Login or Signup to reply.
  2. 0

    You can use a 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search