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, it would be wrong to use where instead of having.
Resources:
Http://www.w3schools.com/sql/sql_groupby.asp
http://www.techonthenet.com/sql/group_by.php
Http://www.w3schools.com/sql/sql_having.asp
http://www.techonthenet.com/sql/having.php
Http://msdn.microsoft.com/en-us/library/ms180199.aspx
Turn from Itgirl smile + add attention
The use of the GROUP by statement and the having statement