HAVING clause is a special type of SQL command. It works in a similar way as  WHERE clause and we use it to define a condition. There are important differences between the two clauses. Let me explain the differences between SQL HAVING and WHERE.

HAVING positioning  in SQL script

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

Why do we have 2 clauses to define conditions? The answer can be found in article about logical processing of SQL query. We can see that the processing order of clauses in the script is following:

1. FROM,
2. WHERE,
3. GROUP BY,
4. HAVING,
5. SELECT,
6. ORDER BY

It is clear from what is mentioned above that e.g. aggregate operations (GROUP BY) are processed after WHERE clause is completed.

Let’s think about where we type some condition based on aggregation SUM(metric)>0? We cannot write SUM(metric)>0 to WHERE clause because SQL engine doesn’t know what is the result of this operation (see clause logical processing order above) – remember WHERE clause is evaluated before GROUP BY. Therefore we need another clause allowing us to do operation like these with aggregations.

Summary:

• In WHERE are written conditions which operate with data/condition in original (non-aggregated) form
• A Condition within Aggregation is being entered in HAVING

HAVING is used mainly to assign condition based on the aggregate function

SQL Having clause Syntax and Example

SELECT [Column 1],[Column 2], SUM([Column 3]) AS Total
FROM Table
WHERE Condition 1...Condition n
GROUP BY [Column 1],[Column 2]
HAVING SUM([Column 3]) <operator> Condition

Lets’s have the table [FactInternetSales]. We will work with only 2 fields:

• Date [OrderDate]
• Amount [SalesAmount]

We want to aggregate the sales amount by date. The result must be sorted in ascending order this time. We also want to display only dates where the sum of all sales in total is higher than 80 000. So..how to do that?

Commutation of SQL HAVING vs WHERE – Error

Let us take a look at what happens when we commutate the clauses. What will happen when we write condition based on aggregate function into WHERE:

SELECT
  [OrderDate]
  ,SUM([SalesAmount]) AS Total
FROM [AdventureWorksDW2012].[dbo].[FactInternetSales]
WHERE SUM([SalesAmount]) > 80000
GROUP BY [OrderDate]
ORDER BY [OrderDate]

the result is an error:

0

How to Write SQL script with HAVING Correctly?

Now let me correct the error from previous step:

SELECT
  [OrderDate]
  ,SUM([SalesAmount]) AS Total
FROM [AdventureWorksDW2012].[dbo].[FactInternetSales]
GROUP BY [OrderDate]
HAVING SUM([SalesAmount])>80000
ORDER BY [OrderDate];

The result is OK:

Combination of SQL HAVING and WHERE

We can obviously combine WHERE and HAVING. Imagine we want only Dates from 2013-06-01 till 2013-10-31 where the sum of sales is higher than 80 000. The condition for date column will be typed into WHERE and condition for aggregation into having as shown on this example. In this case following happens (logical processing) in this order:

1. SQL reads table Fact internet sales based on FROM
2. Table is filtered by dates based on WHERE
3. Then SUM aggregation is performed
4. Then filter on SUM(SalesAmount) si performed
5. Then SELECT/ORDER by is processed

The script was executed successfully without an error. We can see that between 2013-06-01 and 2013-10-31 was 3 days where the sum of the sales is higher than 80 000 USD.