MySQL - Filter records using SUM-CodePudding

I've a student table like below -

CREATE TABLE `test`.`student` (
  `student_id` INT NOT NULL,
  `student_marks` INT NULL);

I want to find the student whose sum of marks is greater than 500, but I am not able to filter the records, however I develop below query.

mysql> select * from student;
 ------------ --------------- 
| student_id | student_marks |
 ------------ --------------- 
|          1 |           220 |
|          2 |           100 |
|          3 |           280 |
|          2 |           430 |
|          1 |           300 |
 ------------ --------------- 
5 rows in set (0.00 sec)

mysql> select student_id, sum(student_marks) from student group by student_id;
 ------------ -------------------- 
| student_id | sum(student_marks) |
 ------------ -------------------- 
|          1 |                520 |
|          2 |                530 |
|          3 |                280 |
 ------------ -------------------- 
3 rows in set (0.00 sec)

CodePudding user response：

You can use the HAVING clause: select student_id, sum(student_marks) from student group by student_id having sum(student_marks) > 500;

CodePudding user response：

We can do like below -

mysql> select * from ( select student_id, sum(student_marks) as marks_of_student from student group by student_id) as f  where f.marks_of_student > 500;
 ------------ ------------------ 
| student_id | marks_of_student |
 ------------ ------------------ 
|          1 |              520 |
|          2 |              530 |
 ------------ ------------------ 
2 rows in set (0.00 sec)