HAVING
clause in SQL is used to filter groups of rows based on specified conditions. It is often used with the GROUP BY
clause to filter groups after aggregate functions have been applied. Unlike the WHERE
clause, which filters rows before grouping, the HAVING
clause filters groups after grouping and aggregation.
HAVING
is used to filter groups of rows based on conditions.GROUP BY
clause to filter groups after aggregation.HAVING
clause often involve aggregate functions like COUNT
, SUM
, AVG
, MIN
, and MAX
.HAVING
is executed after the GROUP BY
clause and before the ORDER BY
clause.column1
: The column(s) by which to group the rows.aggregate_function(column2)
: The aggregate function to apply to each group.table_name
: The table from which to retrieve data.condition
: The condition to filter groups.Employees
that stores employee details.
Table: Employees
EmployeeID | Name | City | State | DepartmentID |
---|---|---|---|---|
1 | Anand | Chennai | Tamil Nadu | 101 |
2 | Bala | Coimbatore | Tamil Nadu | 102 |
3 | Kavitha | Karaikal | Puducherry | 103 |
4 | Raj | Madurai | Tamil Nadu | 101 |
5 | Kumar | Trichy | Tamil Nadu | 102 |
DepartmentID | NumberOfEmployees |
---|---|
101 | 2 |
102 | 2 |
Sales
that stores sales data.
Table: Sales
SaleID | Salesperson | City | State | Amount |
---|---|---|---|---|
1 | Ram | Chennai | Tamil Nadu | 5000 |
2 | Karthik | Coimbatore | Tamil Nadu | 6000 |
3 | David | Bangalore | Karnataka | 5500 |
4 | Kannan | Chennai | Tamil Nadu | 7000 |
5 | Siva | Madurai | Tamil Nadu | 4500 |
City | TotalSales |
---|---|
Chennai | 12000 |
Orders
that stores order details.
Table: Orders
OrderID | CustomerName | City | State | Amount |
---|---|---|---|---|
101 | Ram | Chennai | Tamil Nadu | 5000 |
102 | Karthik | Coimbatore | Tamil Nadu | 6000 |
103 | David | Bangalore | Karnataka | 5500 |
104 | Kannan | Chennai | Tamil Nadu | 7000 |
105 | Siva | Madurai | Tamil Nadu | 4500 |
State | AverageOrderAmount |
---|---|
Tamil Nadu | 5625 |
Students
that stores student details.
Table: Students
StudentID | Name | City | State | Percentage |
---|---|---|---|---|
1 | Ram | Chennai | Tamil Nadu | 92 |
2 | Karthik | Coimbatore | Tamil Nadu | 88 |
3 | David | Bangalore | Karnataka | 95 |
4 | Kannan | Karaikal | Puducherry | 91 |
5 | Siva | Madurai | Tamil Nadu | 89 |
State | AveragePercentage |
---|---|
Karnataka | 95 |
Puducherry | 91 |
HAVING
clause is used to filter groups of rows based on specified conditions.GROUP BY
clause to filter groups after aggregation.HAVING
clause often involve aggregate functions like COUNT
, SUM
, AVG
, MIN
, and MAX
.HAVING
is executed after the GROUP BY
clause and before the ORDER BY
clause.HAVING
to filter groups and generate meaningful summaries and reports.