SQL HAVING clause is used along with GROUP BY clause in any SQL query. The SQL HAVING clause is similar to WHERE clause available in SQL since both the clauses help in filtering data from the result set. However there is a difference in kind of data these two clauses work on. The SQL WHERE clause eliminates initial rows before other processing is done however HAVING clause eliminates rows after it has been processed by GROUP BY clause. Hence, it is mandatory to have GROUP BY clause whenever you are using HAVING clause in SQL Queries. In any SQL Query , in which we have all the three clauses present then WHERE will be executed first followed by GROUP BY and then HAVING.
The general SYNTAX for GROUP BY HAVING CLAUSE can be:
SELECT column1, column2, SUM(column3) FROM table GROUP BY column1,column2 HAVING SUM(column3)> 10;
We need name of all the cities which are having atleast 3 customers for any operator. In that case we will use below query:
SELECT CUST_CITY,COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY HAVING COUNT(*)>2;
Here, we are first doing aggregation on the basis of CUST_CITY and on the result set we are applying filter via HAVING clause to retrieve only those CUST_CITY , wehre count is greater than 2. In HAVING clause, user can use any aggregate function allowed in SQL. Read this article for more information about aggregate functions available in SQL. In TERADATA, we can also specify the position of columns in GROUP BY clause rather than specify the complete column name. Hence below query will also output same result set.
SELECT CUST_CITY,COUNT(*) FROM CUSTOMERS GROUP BY 1 HAVING COUNT(*)>2;