SQL – Having()

Prev     Next

HAVING() function in SQL acts as a filter which is used to specify conditions on aggregate functions in WHERE clause.

The main purpose of HAVING() function is, aggregate functions such as SUM(), COUNT(), AVG() can’t be used in WHERE clause directly. So, they are used in WHERE clause with the help of HAVING() function. Syntax for SQL HAVING() function is given below.

SQL Syntax for HAVING() function:

Syntax for SQL HAVING() function SELECT
column_name,
aggregate_function
(column_name)
FROM table_name
WHERE [conditions] GROUP BY
column_name
HAVING
aggregate_function
(column_name)
operator value;
Syntax for SQL GROUP BY() + HAVING() + ORDER BY() function SELECT
column_name,
aggregate_function
(column_name1)
FROM table_name
WHERE [conditions] GROUP BY
column_name
HAVING
aggregate_function
(column_name1)
operator value
ORDER BY
column_name1,
column_name2;

Note: When we use GROUP BY(), HAVING() and ORDER BY() functions together, the above order must be followed.


Example for how to use SQL HAVING() function:

Please consider the following table with few records as given below.

Table name (for example): student
Column names in this table: Student_ID, Student_name, Department, Marks, City and Age
Available records: 6 rows

sql-table85


Example1 for How to use GROUP BY() function in SQL:

SQL query:

SELECT Student_name, SUM(Marks)
FROM student
WHERE Department in (‘Arts’, ‘English’)
GROUP BY Student_name
HAVING SUM(Marks) > 150;

RUN SQL

SQL query output:

sql-table88

Example2 for How to use GROUP BY() function along with ORDER BY() in SQL:

SQL query:

SELECT Student_name, SUM(Marks)
FROM student
WHERE Department in (‘Arts’, ‘English’)
GROUP BY Student_name
HAVING SUM(Marks) > 150
ORDER BY Marks ASC;

RUN SQL

SQL query output:

sql-table89

List of other inbuilt functions in SQL:

Please click on each SQL functions below to know more about them and sample SQL queries with output.

1. Aggregate functions
SUM() SQL SUM() function returns the sum of a column. The column should be numeric.
COUNT() SQL COUNT() function returns/counts the number of rows in a query. But, it will not count any null values/column.
AVG() SQL AVG() function returns the average value of a column. The column should be numeric.
MIN() SQL MIN() function returns the minimum or smallest value of a column. The column should be numeric.
MAX() SQL MAX() function returns the maximum or biggest value of a column. The column should be numeric.
FIRST() SQL FIRST() function returns the first value of the given column.
LAST() SQL LAST() function returns the last value of the given column.
2. Scalar functions
LEN() SQL LEN() function returns the total length of the given column.
MID() SQL MID() function extracts the characters from a text field.
ROUND() SQL ROUND() function is used to round a numeric value to the nearest integer or to the number of decimals specified by the user.
LCASE() SQL LCASE() function converts the given column to lowercase.
UCASE() SQL UCASE() function converts the given column to uppercase.
NOW() SQL NOW() function is used to return system’s current date & time.
FORMAT() SQL FORMAT() function is used to format a field/column value how it should be displayed in the output.
SQRT() SQL SQRT() function is used to find square root value of the given number.
RAND() SQL RAND() function is used to generate some random numbers.
CONCAT() SQL CONCAT() function is used to concatenate 2 or more strings and forms a single string.
3. Group by function
GROUP BY() GROUP BY() function is used in conjunction with a SELECT statement and aggregate functions (such as SUM(), AVG(), MAX() etc.), to group similar data in result-set by one or more columns.
4. Having function
Having() HAVING() function in SQL acts as a filter which is used to specify conditions on aggregate functions in WHERE clause. The main purpose of HAVING() function is, aggregate functions such as SUM(), COUNT(), AVG(), etc. can’t be used in where clause. So, they are used in where clause with the help of HAVING() function.

Prev     Next

Share this website !!!
Facebook Twitter Linkedin