The use of having and group by in SQL

-------------your own test---------------

Select Sname,sum (Smon)

From student

GROUP BY Sname

Having sum (Smon) >7

---------------your own test-----------------

Here is the text

When both the WHERE clause, the GROUP BY clause, the HAVING clause, and the aggregation function are included, the order of execution is as follows:

Executes a WHERE clause to find data that meets the criteria;

Use the GROUP BY clause to group data;

The GROUP BY clause is formed by running the aggregate function to calculate the value of each group;

Finally, the HAVING clause is used to remove the non-conforming group.

Select Dno,count (*)

From employee

GROUP BY DNO

Having Count (*) >3

Note: Each element in the HAVING clause must also appear in the select list. Some database exceptions, such as Oracle.

Both the HAVING clause and the WHERE clause can be used to set constraints so that the query results meet certain conditional limits.

A HAVING clause restricts a group, not a row. You cannot use the aggregate function in the WHERE clause, but you can in the HAVING clause.

Select Dno,sum (Salary)

From employee

GROUP BY DNO

Having sum (salary) >10000

Small bet

When you set a query condition for a collection function in the SQL language, you use a HAVING clause instead of a WHERE clause. Typically, a HAVING clause is placed at the end of a SQL command.

Aggregate functions, such as SUM, often require the addition of the GROUP by feature.

GROUP by ...

The reason for adding GROUP by to SQL is that the aggregate function (such as SUM) returns the total of all columns each time it is called, and if there is no group BY, the sum of each individual column value combination cannot be computed.

GROUP by Syntax:

SELECT column,sum (column) from table

GROUP by

Column

GROUP by instance

Table "Sales":

W3course 6500
IBM 5500
W3course 7300

Sql:

SELECT Company, SUM (Amount) from Sales

Results:

W3course 19300
IBM 19300
W3course 19300

The above code is invalid because the column returned is not part of the total. The GROUP by clause solves this problem:

SELECT Company,sum (Amount) from Sales

GROUP by Company

Results:

W3course 13800
IBM 5500

Having ...

The reason for having the having join SQL is that where cannot be applied to the aggregate function, and the result condition cannot be tested without having a having.

Having the grammar:

SELECT column,sum (column) from TableGroup by column

Having SUM (column) condition value

Table "Sales":

W3course 6500
IBM 5500
W3course 7300

Sql:

SELECT Company,sum (Amount) from Salesgroup to company

Having SUM (Amount) >10000

Results:

W3course 13800

The use of having and group by in SQL