Skip to the content
Start

HAVING

SQL · Unit 6: Grouping

Which courses need tutoring? At the school, the ones that average under 7.5 get it, and each row of grades is one student's grade in one course.

Last unit, an AVG inside a WHERE gave misuse of aggregate, because the WHERE looks at single rows, before the groups exist. HAVING is the filter that runs afterward. It goes after the GROUP BY, and its condition is tested once per group.

SELECT course_id,
  ROUND(AVG(grade), 2) AS average
FROM grades
GROUP BY course_id
HAVING AVG(grade) < 7.5;

Returns

course_idaverage
16.9
37

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

  1. 1. Complete the code

    Which students take three courses? Each row of grades is one student in one course. Fill in the blank with the word that filters groups.

  2. 2. Predict the output

    A 6 passes. Each student's lowest grade is 7 for student 1, 6 for 2, 5.5 for 3, 7 for 4, 7.5 for 5, and 4 for 6. What does this query return?

  3. 3. Put the lines in order

    Which courses have 3 or more students enrolled? Put in order the query that lists them from the fullest to the least full. One line doesn't belong.

  4. 4. Multiple choice

    The same six grades of 8 or higher, split another way. What does this query answer?

  5. 5. Predict the output

    Students 3 and 5 take three courses each. Student 5 passed all of them, and student 3 failed one, with a 5.5. What does this query return?

  6. 6. Find the bug

    The honor roll lists anyone with a grade of 9 or higher, along with how many courses they take. Henry, student 3, got a 10 in Music, but he isn't on it. Which line has the problem?

  7. 7. Put the lines in order

    The courses table says who teaches each course and how many credits it's worth. Which teachers teach at least two courses worth 2 credits or more? Put the query in order. One line doesn't belong.

  8. 8. Find the case that fails

    This query should list the courses where three or more students passed. A 6 passes. Today Math, History, and Science, which are courses 1, 2, and 3, each have three students passing. Music, course 4, has two enrolled. Whoever wrote it put both conditions in the HAVING. Which of these changes makes it list a course it shouldn't?

  9. 9. Multiple choice

    To leave Music, which is course 4, out of a summary by course, there are two ways: WHERE course_id <> 4 before grouping, or HAVING course_id <> 4 after. They give the same result. Which one would someone who does this for a living write?

  10. 10. Write the code

    Rose teaches two courses: Math, which is course 1, and Science, which is course 3. Which students take both? Write a query with a single column, the student_id. A hint: first keep the rows for those two courses, then look at how many each student has.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →