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.
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!
We might want to select all those customers whose order count is less than two times or more than two, three, four, five etc...
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 SMSS 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.
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.
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.
Finally we need to tell SQL where to group it by and in this case we group it by the CustomerID.
To make it easier to understand we could add an Alias to the Count function that wraps around salesOrderId.
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.
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.
Voila!
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!🥳
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.
I hope you enjoyed this episode, happy learning...
Latest comments (0)