Workaround for SQL where aggregate functions (such as SUM, etc.) cannot be placed behind

When we write more complex SQL statements, we often encounter the need to put sum () into a where, as a conditional query, it turns out that this is not possible, execution will be reported "do not allow the use of grouping function" exception.

So how to fix it, using the HAVING keyword

Example:

Select SUM (amount)
From table

GROUP BY ClientId

Having sum (amount) >100

Special attention:

1. Having to be behind group by (isn't that nonsense?)

2, group by can only be placed after the non-aggregate function of the column

3. The WHERE clause is used to remove rows that do not conform to the where condition before grouping the results of the query, that is, to filter the data before grouping, the condition cannot contain a clustering function, and a where condition to display a particular row.

4. The HAVING clause is the function of filtering the groups that satisfy the condition, that is, filtering the data after grouping, often including clustering functions, using the having condition to display specific groups, or grouping by using multiple grouping criteria.

The HAVING clause is restricted to columns and aggregate expressions that are already defined in the SELECT statement. Typically, you need to reference an aggregate value by repeating an aggregate function expression in the HAVING clause, as you did in the SELECT statement. For example:

SELECT A count (b) from TABLE GROUP by A has COUNT (b) >2

Workaround for SQL where aggregate functions (such as SUM, etc.) cannot be placed behind