SQL HAVING CLAUSE

Posted on Wednesday, 08 March 2023 10:58 AM

SQL HAVING is a clause in SQL that is used to filter groups based on aggregate conditions, such as the result of a COUNT or SUM function. It is used in conjunction with the GROUP BY clause, which groups rows with the same values in one or more columns into summary rows.

HAVING Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

For example,

Let's say we want to retrieve the student IDs and their average marks, but only for students who have taken at least three exams. We can use the HAVING clause to filter the groups that do not meet this condition.

Here's an example query to accomplish this:

SELECT student_id, AVG(marks) as average_marks
FROM exam
GROUP BY student_id
HAVING COUNT(*) >= 3;

OUTPUT

In this example,

We first select the student_id column and the AVG function, which computes the average of the marks column for each group of rows with the same student ID. We then specify the exam table and group the rows by the student_id column using the GROUP BY clause.

The HAVING clause is then used to filter the groups based on the aggregate condition COUNT(*) >= 3, which ensures that we only retrieve results for students who have taken at least three exams.

The result of this query will be a table that contains the student IDs and their average marks, but only for students who have taken at least three exams.

HAVING can be used with a variety of aggregate functions, such as COUNT, SUM, AVG, MIN, and MAX, to filter groups based on aggregate conditions. It can also be used in conjunction with other clauses, such as WHERE and ORDER BY, to further refine the query results.

Overall, HAVING is a powerful tool in SQL for filtering groups based on aggregate conditions, allowing us to retrieve summary statistics for specific subsets of the data.

K-Learn