Tag Archives: having

Not so common SQL concepts

Below are some not so common concepts in SQL server.

  1. Having clause

When we are dealing with aggregate functions like Sum(), count() etc., we generally face difficulty in using them in a WHERE clause.

For example, let us say we have a Customers table which has three columns CustomerID, CustomerName and Product. Because a single customer can buy multiple products, we can insert multiple records for each product a customer buys.

CustomerID CustomerName Product
101 John Bread
102 Smith Milk
101 John Butter

 

Let’s say we want to find the number of customers who purchased at least five products.

The below query will throw an error:

SELECT Customername, count(CustomerName) FROM Customers where count(CustomerName)>=5

Error Message: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

We correct this issue using the below query which uses the having clause:

SELECT Customername, count(CustomerName) FROM cUSTOMERs GROUP BY CustomerName having count(CustomerName)>=5

Note: GROUP BY clause is a must when using having clause.

  1. Exists condition

Syntax: SELECT column_name(s)

FROM table_name

WHERE EXISTS

(SELECT column_name FROM table_name WHERE condition)

The above query first checks if the select query inside the exists condition has data. If yes, it will return the values in the actual query.

Advertisements