【译文】如何在ACCESS SQL中使用聚合函数

时　间:2014-03-24 09:17:38
作　者:周芳   ID：24526  城市：上海
摘　要: 聚合函数用于计算统计和汇总信息表中的数据。在SELECT语句中使用这些函数,所有字段或表达式都可以作为参数。
正　文:

来自：微软    翻译：周芳

【译文】如何在ACCESS SQL中使用聚合函数

      聚合函数用于计算统计和汇总信息表中的数据。在Select语句中使用这些函数,所有字段或表达式都可以作为参数。

      计算一个结果集记录的数量,使用计数函数。在计数函数中使用星号使空值也可以同样计算。

VBA
Select Count(*) AS [Number of Invoices]
    FROM tblInvoices

      计算非空值时，在计数函数中使用一个字段名
VBA
Select Count(Amount) AS
    [Number of Valid Invoice Amounts]
    FROM tblInvoices

      要找出数据中一列的平均值，使用Avg函数
VBA
Select Avg(Amount) AS [Average Invoice Amount]
    FROM tblInvoices

      要找出数据中一列的总数，使用Sum函数
VBA
Select Sum(Amount) AS [Total Invoice Amount]
    FROM tblInvoices

      要找出一列中的最小值，使用Min函数
VBA
Select Min(Amount) AS [Minimum Invoice Amount]
    FROM tblInvoices

      要找出一列中的最大值，使用Max函数
VBA
Select Max(Amount) AS [Maximum Invoice Amount]
    FROM tblInvoices

      要找出一列中第一个值，使用First函数
VBA
Select First(Amount) AS [First Invoice Amount]
    FROM tblInvoices

      要找出一列中最后一个值，使用Last函数
VBA
Select Last(Amount) AS [Last Invoice Amount] 
    FROM tblInvoices

【原文】How to: Use Aggregate Functions to Work with Values in Access SQL

       Aggregate functions are used to calculate statistical and summary information from data in tables. These functions are used in Select statements, and all of them take fields or expressions as arguments.

      To count the number of records in a result set, use the Count function. Using an asterisk with the Count function causes Null values to be counted as well.
VBA
Select Count(*) AS [Number of Invoices]
    FROM tblInvoices

To count only non-Null values, use the Count function with a field name.

VBA
Select Count(Amount) AS
    [Number of Valid Invoice Amounts]
    FROM tblInvoices

To find the average value for a column or expression of numeric data, use the Avg function.

VBA
Select Avg(Amount) AS [Average Invoice Amount]
    FROM tblInvoices

To find the total of the values in a column or expression of numeric data, use the Sum function.

VBA
Select Sum(Amount) AS [Total Invoice Amount]
    FROM tblInvoices

To find the minimum value for a column or expression, use the Min function.

VBA
Select Min(Amount) AS [Minimum Invoice Amount]
    FROM tblInvoices

To find the maximum value for a column or expression, use the Max function.

VBA
Select Max(Amount) AS [Maximum Invoice Amount]
    FROM tblInvoices

To find the first value in a column or expression, use the First function.

VBA
Select First(Amount) AS [First Invoice Amount]
    FROM tblInvoices

To find the last value in a column or expression, use the Last function.

VBA
Select Last(Amount) AS [Last Invoice Amount] 
    FROM tblInvoices