How would one return the students that are below the average in SQL query

For my assignment, we have to return the count of students that are below the average of the average where students are in section_id 86 and the grade_type_code is ‘FI’ I feel like I have it structured right because I’m able to return all the results just I can not figure out how to have them be less than or below what the average is. Here is what I have thanks!

EDIT: Have to use subqueries to achieve this goal.

SELECT COUNT(g.student_id) BELOW_AVERAGE
FROM grade g
WHERE g.section_id= 86
  AND g.grade_type_code = 'FI'
HAVING AVG(numeric_grade) = (SELECT AVG(below_avg)
                             FROM
                                 (SELECT AVG(g.numeric_grade) below_avg
                                  FROM grade g
                                  WHERE g.section_id = 86
                                    AND g.grade_type_code = 'FI'))

Answer

Use AVG as an analytic function so that you don’t have to query the table twice:

SELECT COUNT( DISTINCT student_id ) AS number_below_average
FROM   (
  SELECT student_id,
         numeric_grade,
         AVG( numeric_grade ) OVER () as avg_numeric_grade
  FROM   grade
  WHERE  section_id = 86
  AND    grade_type_code = 'FI'
)
WHERE  numeric_grade < avg_numeric_grade

Or, if you must use two queries:

SELECT COUNT( DISTINCT student_id ) AS number_below_average
FROM   grade
WHERE  section_id = 86
AND    grade_type_code = 'FI'
AND    numeric_grade < (
                         SELECT AVG( numeric_grade )
                         FROM   grade
                         WHERE  section_id = 86
                         AND    grade_type_code = 'FI'
                       )

Which, for the sample data:

CREATE TABLE grade ( student_id, numeric_grade, section_id, grade_type_code ) AS
SELECT  1, 10, 86, 'FI' FROM DUAL UNION ALL
SELECT  2, 11, 86, 'FI' FROM DUAL UNION ALL
SELECT  3, 12, 86, 'FI' FROM DUAL UNION ALL
SELECT  4, 13, 86, 'FI' FROM DUAL UNION ALL
SELECT  5, 14, 86, 'FI' FROM DUAL UNION ALL
SELECT  6, 15, 86, 'FI' FROM DUAL UNION ALL
SELECT  7, 16, 86, 'FI' FROM DUAL UNION ALL
SELECT  8, 17, 86, 'FI' FROM DUAL UNION ALL
SELECT  9, 18, 86, 'FI' FROM DUAL UNION ALL
SELECT 10, 19, 86, 'FI' FROM DUAL UNION ALL
SELECT 11, 20, 86, 'FI' FROM DUAL;

Then both queries output:

| NUMBER_BELOW_AVERAGE |
| -------------------: |
|                    5 |

db<>fiddle here