Search code examples
mysqlsqlsum

Retrieve row where sum of column is greater than other column of different table

I have two database tables.

First table:

| ID  |  Sub-Name |  Marks
| 01  |  french   |  50
| 01  |  russian  |  50
| 02  |  french   |  30
| 02  |  russian  |  50
| 03  |  french   |  20
| 03  |  russian  |  30

Second table:

|  ID | Stu-name | passing_marks
|  01 | abc      | 90
|  02 | xyz      | 90
|  03 | mno      | 90

I want to get the names of students whose collective marks of French and Russian are greater the passing_marks of table2.

Solution

  • Please , First declare primarykey and foreignkey relation with table with proper table name

    Here I take 1st named detail and second table name is student

    SELECT student.ID, student.Stu_name, detail.collective
FROM student
JOIN  
(
   SELECT ID, SUM(Marks) AS collective
   FROM detail GROUP BY ID
) detail
ON student.ID = detail.ID
AND detail.collective> student.passing_marks;