I have two tables: Profile and Record. In Profile, I have student names and other information, whereas, in Record, I have rows with curriculum information. In the Record table, a single student can have many rows. I need to calculate the number of rows grouped by student_name, but only retrieve those student names with more rows than the average (number of rows/total number of students). I can find the number of rows grouped by student_name, but I can't write a subquery to display only those above average. Could someone please explain a method to me?

This is what I have for now:

SELECT student_name, COUNT(*)
  FROM Profile p 
  JOIN Record r
    ON p._id = r._id
 GROUP BY student_name

The desired output is only to retrieve students_name with the above avg no. of rows in the Records table:

CodePudding user response：

Indeed you can use sub-query to get your desired output.

Code:

SELECT student, COUNT(*) AS no_of_records  
FROM record
GROUP BY student
HAVING no_of_records > (SELECT COUNT(*)/COUNT(DISTINCT student) FROM record);

Explanation:

• The sub-query here will return average row count from record table
• In the outer-query, we are calculating number of rows for each student and comparing it with sub-query's result

Note: You can join it with profile table in outer query if needed.

Look at the fiddle to understand it better