Learn SQL: Microsoft SQL Server - Episode 9: Aggregation in Where Clause
Tags: beginners, sql, database, datascience
Published: 2021-11-10 18:48:00

![goran-says-hi](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9tele45r212abw91j336.png)

Many times we might need to use aggregated values in our **Where Clause**, as a way of limiting the result set of aggregated values. You would think it would be as easy as adding a **Where Clause** in our query statement, however we will see in this episode, this is not the case.

![skid-12](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ix1pr53eupqnfaio6gsb.gif)

By using the previous episode as an example, you can check that out on the link below to get up to speed. 

>It's a quick read so once you're done head on back here! 

![skid-cover-4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0me8zjay4n4i68r464ie.png)

{% link https://dev.to/ifierygod/learn-sql-microsoft-sql-server-episode-8-null-values-in-aggregation-22d2 %}. 

We might want to select all those customers whose order count is less than two times or more than two, three, four, five etc...

![skid-11](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r8281hpafx5y20o5ahz4.gif)

The **Count** is in this example the aggregated value. However a **Where Clause** does not take in an aggregated value. In this episode we are going to discuss how to use aggregated values to select our records.

Lets move into <abbr title="SQL Server Management Studio">SMSS</abbr> and see this in practice.


In this example we will be using the **Sales.SalesOrderHeader** table. As we know this table stores the information for every order the customer has placed. There is essentially one record for each sales order, then there are **CustomerID's** that stores information about the customer who placed this order.

![skid-11](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r8281hpafx5y20o5ahz4.gif)

What we are trying to do here is to find the **CustomerID** _Where_ the sales count is more ``than X / less than X``

First we will start by using a **SELECT** statement and select the **CustomerID** and the **SalesOrderID**.

![discussion-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4vcyz11kumb2zrzk4mkj.gif)

What we need now is the **Count** of **Sales Orders**. Therefore we wrap the **salesOrderId** in the **Count** function. Notice we are grouping by the **Customer level**, meaning for each customer give us the count of their **salesOrderId's**. 

![skid-7](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kuug4oqwb2fnr1gwn4xm.gif)

Finally we need to tell SQL where to group it by and in this case we group it by the **CustomerID**.

![discussion-2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fxv72b9ai3791f4dulsi.gif)

To make it easier to understand we could add an **Alias** to the **Count** function that wraps around **salesOrderId**.

![skid-7](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kuug4oqwb2fnr1gwn4xm.gif)

In order to only select the _customers_ whose _sales order_ _Count_ is less than two, in this scenario, normally we would use the **Where clause**. However if we try this we will see SSMS throws an error.

![discussion-3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8oid9kl767fuu10601z4.gif)

Instead of **Where**, we replace it with **Having**. Also notice we cannot use an **Alias** when we use **Having**, we must use the complete **Count** function query string containing the **salesOrderId**.

![discussion-4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tfx32rkh0ly1zixlmb9y.gif)

Voila! 

![skid-12](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ix1pr53eupqnfaio6gsb.gif)

Now we can see all the _customers_ whose _sales order_ _Count_ is less than two. We could even change the operator to less than equal to two, which in this case will return customers whose sale orders are two and less than two in the result set.

Awesome right!🥳

![sql](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jpchmuphmj1xueik4m90.png)

>Keep in mind, for any of the _aggregated functions_, such as **MIN**, **MAX**, **AVG** or **SUM**, you will have to use **HAVING** if you want to restrict your result set, by those aggregated values.

![skid-cover-4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0me8zjay4n4i68r464ie.png)


I hope you enjoyed this episode, happy learning...

![meditation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s61js9hqsv5datguo9hu.gif)