Usage of group by having in sql

GROUP BY and HAVING clauses are used to group and filter SQL query results. GROUP BY divides rows into groups, while HAVING filters groups that meet specific criteria.

Usage of GROUP BY and HAVING clauses in SQL

Introduction:
GROUP BY and HAVING clauses are advanced aggregate functions in SQL used to group and filter query results.

GROUP BY clause:
The GROUP BY clause is used to divide the rows in the result set into different groups. Groups are divided based on one or more columns, called grouping columns. All rows in each group share the same grouping column value.

Syntax:

<code>SELECT aggregate_func(column_name)
FROM table_name
GROUP BY column_name1, column_name2, ...</code>

HAVING clause:
The HAVING clause is used to filter the groups produced by the GROUP BY clause. It only selects groups that meet certain criteria. Conditions can be based on the results of aggregate functions.

Syntax:

<code>SELECT aggregate_func(column_name)
FROM table_name
GROUP BY column_name1, column_name2, ...
HAVING condition</code>

Use case:

• Find each product category in which the total sales exceed a specific value Products
• Calculate the average salary for each department and filter out departments whose average salary is higher than the company average
• Find each customer who purchased a specific quantity of items

Difference:
The GROUP BY clause groups rows, while the HAVING clause filters the groups produced by the GROUP BY clause. The GROUP BY clause must precede the HAVING clause.

Example:

Find every product category with total sales over $1000:

<code>SELECT product_category, SUM(sales) AS total_sales
FROM sales_table
GROUP BY product_category
HAVING total_sales > 1000</code>

The above is the detailed content of Usage of group by having in sql. For more information, please follow other related articles on the PHP Chinese website!