The operation of the Aggregate function targets a series of values and returns a single value.
Note: If a SELECT statement is used in many other expressions in the list of items in the SELECT statement, the select must use the GROUP by statement!
First, all SQL instances are used to the following table
o_id |
OrderDate |
Orderprice |
Customer |
1 |
2008/12/29 |
1000 |
Bush |
2 |
2008/11/23 |
1600 |
Carter |
3 |
2008/10/05 |
700 |
Bush |
4 |
2008/09/28 |
300 |
Bush |
5 |
2008/08/06 |
2000 |
Adams |
6 |
2008/07/21 |
100 |
Carter |
SQL AVG
The AVG function returns the average of a numeric column. NULL values are not included in the calculation.
Grammar
SELECT AVG (column_name) from table_name
Example 1, calculating the average of the "Orderprice" field
SELECT AVG (Orderprice) as Orderaverage from Orders
The results are as follows
Example 2, find customers with Orderprice values above the Orderprice average
Select Customerfrom orderswhere orderprice> (select AVG (orderprice) from Orders)
The results are as follows
Customer |
Bush |
Carter |
Adams |
SQL COUNT
The count () function returns the number of rows that match the specified condition (Null does not count in)
SELECT COUNT (column_name) from table_name
SQL COUNT (*) returns the number of records in the table
SELECT COUNT (*) from table_name
SQL COUNT (DISTINCT column_name) returns the number of different values for the specified column
SELECT COUNT (DISTINCT column_name) from table_name
Note: COUNT (DISTINCT) applies to Oracle and SQL SERVER, but is not available for access
Example 1, calculating the number of orders for the customer "Carter"
SELECT COUNT (Customer) as Customernilsen from Orderswhere customer= ' Carter '
The result of the above SQL statement is 2 because there are 2 orders for customer Carter:
Example 2, using count (*) to get the total number of rows in a table
SELECT COUNT (*) as Numberofordersfrom Orders
The result set looks like this:
Example 3, using count (DISTINCT column_name) to calculate the number of different customers in the Orders table
SELECT COUNT (DISTINCT Customer) as Numberofcustomersfrom Orders
The result set looks like this:
This is the number of different customers (Bush, Carter, and Adams) in the Orders table.
SQL First
The first () function returns the value of record one in the specified field
Records can be sorted by using the ORDER BY statement
Grammar
SELECT First (column_name) from table_name
Instance to find the first value of the "Orderprice" column
SELECT First (Orderprice) as Firstorderprice from Orders
The result set looks like this:
SQL Last
The last () function returns the value of the final record in the specified field
Records can be sorted by using the ORDER BY statement
Grammar
SELECT last (column_name) from table_name
Instance to find the last value in the "Orderprice" column
SELECT last (Orderprice) as Lastorderprice from Orders
The result set looks like this:
SQL MAX
The max () function returns the maximum value in a column. Null values are not included in the calculation
Grammar
SELECT MAX (column_name) from table_name
MIN and MAX can also be used for text columns to get the highest or lowest values in alphabetical order
instance, finding the maximum value for the "Orderprice" column
SELECT MAX (Orderprice) as Largestorderprice from Orders
The result set looks like this:
SQL MIN
The min () function returns the minimum value in a column. Null values are not included in the calculation
Grammar
SELECT MIN (column_name) from table_name
MIN and MAX can also be used in text columns to get the highest or lowest values in alphabetical order.
instance to find the minimum value in the "Orderprice" column
SELECT MIN (Orderprice) as Smallestorderprice from Orders
The result set looks like this:
SQL SUM
The sum () function returns the total number of numeric columns
Grammar
SELECT SUM (column_name) from table_name
Instance to find out the total number of "Orderprice" fields
SELECT SUM (Orderprice) as OrderTotal from Orders
The result set looks like this:
5700= 1000+1600+700+300+2000+100
SQL aggregate function (AVG, Count, first, last, Max, Min, sum)