The use of the group BY statement and the having statement in SQL

First, GROUP by

The GROUP by statement is used with aggregate functions (aggregate functions such as COUNT, SUM, AVG, MIN, or MAX.) Federated use to get the result set of one or more columns.

The syntax is as follows:

SELECT column1, Column2, ... column_n, aggregate_function (expression)

From tables

WHERE predicates

GROUP by Column1, Column2, ... column_n;

Example

For example, we have a student form (student) with a number of columns (ID), course (course), score (score) and so on, and we want to get each student selected by the query, and we can use the Count function together with group By statement to get this result

SELECT ID, COUNT (course) as Numcourse

From student

GROUP by ID

Because we are using the number to group, so that the count function is in the study number of the premise down to achieve, through the count (course) can calculate each number corresponding to the number of courses.

Attention

Because an aggregate function returns only a single value by acting on a set of data, the element that appears in the SELECT statement is either the input value of an aggregate function or the parameter of the group BY statement, otherwise an error occurs.

For example, for the table mentioned above, we make a query like this:

SELECT ID, COUNT (course) as Numcourse, score

From student

GROUP by ID

At this point the query will be faulted with the following error:

Column ' Student.score ' is invalid in the select list because it's not contained in either an aggregate function or the GR OUP by clause.

The reason for the above error is because a student ID corresponds to multiple scores, and if we simply write score in the SELECT statement, we cannot determine which score should be output. If you want to use score as a parameter of a SELECT statement, you can use it as an input value for an aggregate function, as in the following example, we can get the number of courses selected by each student and the average score for each student:

SELECT ID, COUNT (course) as Numcourse, AVG (score) as Avgscore

From student

GROUP by ID

Second, having

A having statement is typically used in conjunction with a GROUP BY statement to filter the recordset returned by the group by statement.

The existence of a having statement compensates for deficiencies in the where keyword cannot be used in conjunction with aggregate functions.

Grammar:

SELECT column1, Column2, ... column_n, aggregate_function (expression)
From tables
WHERE predicates
GROUP by Column1, Column2, ... column_n
Having condition1 ... condition_n;

Also use the Student form in this article, if you want to query a student record with an average score higher than the score, you can write:

SELECT ID, COUNT (course) as Numcourse, AVG (score) as Avgscore

From student

GROUP by ID

Having AVG (score) >=80;

In this case, there will be an error if you replace the having with where .

The use of the group BY statement and the having statement in SQL