SQL Having usage example summary

SQL Having Usage

The HAVING clause is used to filter the data after a group.

1. If an aggregate function is used in the filtering condition, or the rows have been grouped, HAVING must be used to replace WHERE. Otherwise, an error is reported.

2. HAVING must be declared behind GROUP BY.

# Check the information of departments with a maximum salary of more than 10,000 in each departmentSELECT department_id, MAX(salary) max_salary
FROM employees
GROUP BY department_id
# Filter the grouped data and select departments with a maximum salary of more than 10,000 in the departmentHAVING max_salary &gt; 10000;

During development, the premise of using HAVING is that GROUP BY is used in SQL.

If GROUP BY is not used, HAVING is used, then all records in the entire table are treated as a group, and the set of records is then filtered according to the conditions in the HAVING.

SELECT department_id, MAX(salary) max_salary
FROM employees
HAVING max_salary > 10000;

HAVING cannot be used alone, it must be used with GROUP BY.

1. When there is an aggregate function in the filter condition, this filter condition must be declared in HAVING.
2. When there is no aggregate function in the filter condition, the filter condition declaration is OK in WHERE or HAVING. However, it is recommended that you declare it in WHERE.

Supplement: SQL Having Usage

Take a business scenario to talk about this case. For example, we have a table that may contain corrected data. The corrected data will not be overwritten with the newly corrected data, but will be inserted into the table one by one. If we want to find the data containing the earliest corrected and the latest corrected data, then we need to use the usage of havinng. Assuming the initial batch number is 000 and the batch number is accumulated.

For example, we have a table table with two fields: order number (order_no) and order number (endorseq_no)

Then the writing method is

select order_no
from table
group by order_no
having sum(case when endorseq_no = '000' then 1 
                else 0 end) &gt;= 1  --This is a judgment that must include the initial batch order
  and count(*) &gt;= 2    --This is to judge that in addition to the initial subscription, there is also a new subscription.

This is all about this article about the usage of SQL Having. For more related content on SQL Having, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!