CODEWITHSUNDEEP
mysqlsql
Sound of Speed
Sound of Speed

Reputation: 61

SQL - How to list items which are below the average

I have quite a basic databast of 3 tables. "Students" "Tests" and "Scores"

For each test I need to list all students with test scores that are below the average for that test. (If that makes any sense at all)

I have an SQL query which simply prints the average score for each test.

SELECT t.Test_name, AVG(sc.Result) AS Avgscore
FROM Tests t 
JOIN Scores sc ON t.id_Tests = sc.Tests_id_Tests
JOIN Students s ON sc.Students_id_Students = s.id_Students
WHERE t.id_Tests = $c"

($c is a parameter from a for loop, which is incrementing to printing out each test as a separate table)

Any help would be appreciated, thanks

Upvotes: 2

Views: 1249

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Change the select list for whatever columns you want to display, but this will limit the results as you want, for a given testid (replace testXYZ with the actual test you're searching on)

SELECT t.Test_name, s.*, sc.*
  FROM Tests t
  JOIN Scores sc
    ON t.id_Tests = sc.Tests_id_Tests
  JOIN Students s
    ON sc.Students_id_Students = s.id_Students
 WHERE t.id_Tests = 'textXYZ'
   and sc.result <
       (select avg(x.result)
          from scores x
         where sc.Tests_id_Tests = x.Tests_id_Tests)

Note: To run this for ALL tests, and have scores limited to those that are below the average for each test, you would just leave that one line out of the where clause and run:

SELECT t.Test_name, s.*, sc.*
  FROM Tests t
  JOIN Scores sc
    ON t.id_Tests = sc.Tests_id_Tests
  JOIN Students s
    ON sc.Students_id_Students = s.id_Students
 WHERE sc.result <
       (select avg(x.result)
          from scores x
         where sc.Tests_id_Tests = x.Tests_id_Tests)

Upvotes: 2

Daniel Sparing
Daniel Sparing

Reputation: 2173

For example in PostgreSQL you could use a window function like AVG(Score) OVER (GROUP BY id_Tests), but in MySQL I suggest using a subquery as follows:

SELECT Scores.*, Students.*, t.Test_name, Avgscore
FROM Scores
JOIN Students ON sc.Students_id_Students = s.id_Students
JOIN 
    SELECT id_Tests, t.Test_name, AVG(sc.Result) AS Avgscore
    FROM Tests t 
    JOIN Scores sc ON t.id_Tests = sc.Tests_id_Tests
    -- WHERE id_Tests = $c
    GROUP BY id_Tests, t.Test_name
) avgsc ON Scores.Tests_id_Tests=avgsc.id_Tests
WHERE Scores.Result < Avgscore

Note that a student can be listed multiple times if they got below average score multiple times -- might or might not be what you want.

I commented out the line filtering the test as I guess it is easier to retrieve all tests at once, but if you insist on filtering on one test on application level then you can filter here by uncommenting it.

Upvotes: 0

Related Questions