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

Printing all records of a student who failed one subject – Mysql

I have a usecase on my studies for below example,
i am struggling with the logic on how i can do a select statement to get all the records to display a student who failed in one or more subject

Assuming i have below table

Student_id name subject marks
s1 adam english 35
s1 adam math 69
s1 adam science 55
s2 bob english 56
s2 bob math 76
s2 bob science 88
s3 pop english 33
s3 pop math 90
s3 pop science 76

Expected output should be

Student_id name subject marks
s1 adam english 35
s1 adam math 69
s1 adam science 55
s3 pop english 33
s3 pop math 90
s3 pop science 76

I have tried with this below query but it displaying none value

select count(*) 
from (select student_id 
      from student 
      where marks < 50 
      group by student_id 
      having count(subject) > 1) as dt
Tags:

3

Answers


  1. 0 
        SELECT *
FROM student_scores
WHERE Student_id IN (
  SELECT Student_id
  FROM student_scores
  WHERE marks < 40
  GROUP BY Student_id
  HAVING COUNT(*) >= 1
)

    This query will first select all records from the marks table where the marks are less than 40. It will then group the records by Student_id and count the number of records in each group

    Login or Signup to reply.
  2. 0

    You can do it using SUM OVER() clause as follows :

    This does the group by Student_id. And it calculates the number of failed object using SUM CASE clause

    with cte as (
  select *, sum(case when marks <50 then 1 else 0 end) over (partition by Student_id) as failed
  from student
)
select Student_id, name, subject, marks
from cte
where failed >= 1

    Demo here

    Login or Signup to reply.
  3. 0

    I think something like this would work and is pretty simple:

    Select * 
  From student 
  Where student_id In (Select Distinct student_id 
                         From student 
                         Where marks < 50);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search