Category Archives: SQL

Both clauses in SQL statements are filtering a specific output, but there’s a significant difference. Here’s what Wikipedia tells us:

A HAVING clause in SQL specifies that an SQL SELECT statement should only return rows where aggregate values meet the specified conditions.

Aggregate Functions are the following:

AVG

COUNT

FIRST

LAST

MAX

MIN

SUM

So HAVING clauses can only be used if you are using an aggregate function with a GROUP BY clause. I’ve set up a small table on MS SQL Server to try this out:

Name

Wins

Toby

23

Toby

11

Lisa

16

John

4

John

7

Here is our SELECT statement for this table and its output:

SELECT Name, SUM(Wins)
FROM WinRecords
GROUP BY Name

Name

Wins

John

11

Lisa

16

Toby

34

This grouped the results by the names of our entries. But what if we want to display entries with more than 10 wins? We can try using a WHERE clause:

SELECT Name, SUM(Wins)
FROM WinRecords
WHERE Wins > 10
GROUP BY Name

Name

Wins

Lisa

16

Toby

34

That didn’t work as we expected it to. John has a total of 11 wins, so he should be displayed too. That’s because the WHERE clause runs before the aggregate functions, while HAVING runs after the aggregate values have been calculated.

Here’s the query with HAVING:

SELECT Name, SUM(Wins)
FROM WinRecords
GROUP BY Name
HAVING SUM(Wins) > 10

Name

Wins

John

11

Lisa

16

Toby

34

Note: You can’t use aliases in HAVING clauses, that’s why I typed SUM(Wins) out.

This query would exclude everyone with less than 10 wins in total. Pretty cool, huh?