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 

T-SQL: Aggregate Functions; Date Functions; and Group By Statement Woes

When combining Aggregate Functions with Date / Time functions within an SQL Query, you may run into errors relating to the “Group By” statement, such as:

  • “Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”
  • “Invalid Column Name”
  • “Each GROUP BY expression must contain at least one column that is not an outer reference”

Here are some examples of how NOT to formulate the Query.

SELECT processed_by,
year(date_received) as year_received,
COUNT(date_sent) AS quotes_sent,
AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received,
AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required
FROM dbo.windows_quote
WHERE date_sent IS NOT NULL
GROUP BY processed_by
Msg 8120, Level 16, State 1, Line 2
Column ‘dbo.windows_quote.date_received’ is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY clause.

Excluding “year_received” from the GROUP BY statement does not work, and neither does adding it.

SELECT processed_by,
year(date_received) as year_received,
COUNT(date_sent) AS quotes_sent,
AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received,
AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required
FROM dbo.windows_quote
WHERE date_sent IS NOT NULL
GROUP BY processed_by, year_received
Msg 207, Level 16, State 1, Line 8
Invalid column name ‘year_received’.

I also tried to reference the field by index number..

SELECT processed_by,
year(date_received) as year_received,
COUNT(date_sent) AS quotes_sent,
AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received,
AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required
FROM dbo.windows_quote
WHERE date_sent IS NOT NULL
GROUP BY processed_by, 2
Msg 164, Level 15, State 1, Line 8
Each GROUP BY expression must contain at least one column that is not an outer reference.

Only by including the function itself within the GROUP BY statement could I get the Query to run properly.

SELECT processed_by,
year(date_received) as year_received,
COUNT(date_sent) AS quotes_sent,
AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received,
AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required
FROM dbo.windows_quote
WHERE date_sent IS NOT NULL
GROUP BY processed_by, year(date_received)