Examples of SQLServer2008 aggregate functions

Create the Tb_students table and insert 3 test data.
?

1 2 3 4 5 6 7

CREATE TABLE tb_students (id int not null IDENTITY, name NVARCHAR NOT NULL, age int. NOT NULL, PRIMARY KEY (ID)) INSERT Into Tb_students (name,age) VALUES (' Tiana ',), (' Yardi ',), (' Zhangyun ', 18)

1 SUM () function

Returns the value of a column.

Cases:

SQL query:

?

1	SELECT SUM (age) from Tb_students WHERE age>15

Execution results:

Description

Returns the sum of the ages of students older than 15:120+18=38.

2 COUNT () function

Returns the number of rows in a column.

Cases:

SQL query:

?

1	SELECT COUNT (*) from tb_students WHERE age>15

Execution results:

Description

Returns the number of rows for student records older than 15.

Of course, here you can use COUNT (ID), count (name), and Count (age) to find the same results.

3 AVG () function

Returns the average of a column.

Cases:

SQL query:

?

1	SELECT AVG (age) from Tb_students WHERE age>15

Execution results:

4 MAX () function

Returns the maximum value for a column.

Cases:

SQL query:

?

1	SELECT MAX (age) from Tb_students WHERE age>15

Execution results:

5 MIN () function

Returns the minimum value of a column.

Cases:

SQL query:

?

1	SELECT MIN (age) from Tb_students WHERE age>15

Execution results:

6 aggregate functions are used with the GROUP BY clause

Sum (), COUNT (), AVG (), MAX (), min () function, when used with the GROUP BY clause, can be used to calculate the sum of each grouping, the total number of records, the average, the maximum, and the minimum value.

Cases:

Create a table Tb_class and insert 8 test data.

SQL query:

?

1 2 3 4, 5 6 7 8 9 10 11 12 13 14 15

CREATE TABLE tb_class (classid int not null IDENTITY, classname NVARCHAR NOT NULL, students int NOT NULL, teacher NVAR CHAR (not NULL, PRIMARY KEY (CLASSID)) inserts into Tb_class (classname,students,teacher) VALUES (' Class 1 ', ', ' yl '), (' Class 2 '), (' wsp '), (' Class 3 ', ' yl '), (' Class 4 '),, ' Zhy '), (' Class 5 ', ', ' wsp '), (' Class 6 ', ' yl '), (' Class 7 ', ', ' Zhy '), (' Class 8 ', ', ' wsp '), SELECT SUM (students) from Tb_class GROUP by teacher Select COUNT (students) from Tb_class Group by teacher SELECT AVG (students) FR OM tb_class GROUP by teacher Select MAX (students) from Tb_class GROUP by teacher Select MIN (students) from Tb_class Group by teacher

Execution results:

7 Aggregation of different values

Cases:

SQL query:

?

1	SELECT COUNT (DISTINCT teacher) from Tb_class

Execution results:

Description: When using a clustered function with a distinct clause, you can remove duplicate records and then use the aggregate function to perform the operation.

The query in the instance queries the number of teachers in the class table.