T-SQL: Using the HAVING clause

Here is a quick differentiation between the “WHERE” clause and the “HAVING” clause within an SQL Query.

Below is a basic query that will generate a list of the Customers based upon the number of orders they have placed.

select count(order_no) as Orders, slcust_no
from windows_enquiry
where order_no >0
group by slcust_no
order by orders desc
SQL Having 002

I have used the “WHERE” clause to filter records where the Order Number is greater than zero (you could also filter by order date).

However, if I am only interested in Customers that have placed more than 100 orders, I can use the HAVING clause as shown below.

select count(order_no) as Orders, slcust_no
from windows_enquiry
where order_no >0
group by slcust_no
having COUNT(order_no) > 100
order by orders desc
SQL Having 001

The HAVING clause allows me to apply a filter based upon a value created by the GROUP BY clause (eg SUM; COUNT; AVG), rather than values in the original table.

If you only need to list your top 10 customers, use the TOP clause or the LIMIT clause.

select top 10
count(order_no) as Orders, slcust_no
from windows_enquiry
where order_no >0
group by slcust_no
having COUNT(order_no) > 100
order by orders desc
select count(order_no) as Orders, slcust_no
from windows_enquiry
where order_no >0
group by slcust_no
having COUNT(order_no) > 100
order by orders desc
limit 10 
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s