Introduction to SQL Aggregate Functions Overview
A SQL aggregate function is a procedure that acts on a group of records, such as the values of a field of a table or query, and produces a statistical value from the group. The general syntax of SQL aggregate functions is: Public Function FunctionName(ColumnName) As Variant Practical Learning: Introducing Summary Queries
A Summary Query A summary query is a query on which a SQL aggregate function is applied to produce a statistical value. To create a summary query, you can use the Query Wizard or display one in Design View. If you are working in the Design View, to make a query summarize its data:
After applying the Totals feature, each column of the query would become equipped with a row named Total. Although you can create a summary query with all the fields or any field(s) of a query, the purpose of the query is to summarize data, not to review the records. For a good summary query, you should select a column where the records hold categories of data. This means that the records in the resulting list have to be grouped by categories. Practical Learning: Starting a Summary Query
SQL Aggregate Functions Summarizing the Values To visually apply a SQL aggregate function, display the query in the Design View. Using either the table in the top side of the window or the column in the bottom side, select the field that holds the values for the statistics. In the bottom side of the window and in the Total row, select the name of the desired function. Each SQL aggregate function has an appropriate name. Their general syntax is: Public Function functionname(ByVal expression As Variant) As Variant The Number of Records The function used to get the total number of records of a column is named Count. This function can be applied to a column of any type. It returns an integer as the number of records, including empty fields. Practical Learning: Counting the Number of Records
The Sum of Values If a column holds numeric values, to let you get their sum, the SQL provides a function named Sum. If you apply the Sum() function on a nonnumeric field, you will receive an error. Here is an example:
Practical Learning: Getting a Statistical Sum
The Mean To let you get the mean of the values of a field, the SQL provides a function named Avg. Like the Sum() fuction, the average should be performed only on a numberbased column. Here is an example: Practical Learning: Getting the Average of a Series
The First Record of a Field To let you get the first value among the records of a field, the SQL provides a function named First. This function produces the value as it is available in the first record. If there is no actual value in the first record, the function produces null (or an empty value). Practical Learning: Getting the First Record of a Field
The Last Record of a Field The function to get the last value of a field is named Last. It works like its First() counterpart except that it considers the last occurrence of the records. Practical Learning: Getting the Last Record of a Field




The Lowest Value To let you get the lowest of the values of a field, the SQL provides an aggregate function named Min. It works on all types of field as follows:
Practical Learning: Getting the Lowest Value of a Field
The Highest Value To let you get the highest value of a field, the SQL provides the Max() aggregate function. It works like its Min() counterpart, except that it returns the highest value. If the field uses Boolean values, if there is a True value or a checked box, the function returns 0. Practical Learning: Getting the Last Record of a Field
The Standard Deviation To let you calculate the standard deviation of the values of a field, the SQL provides a function named StdDev. The standard deviation also is calculated only a numberbased field. Practical Learning: Getting the Standard Deviation
The Variance of a Series To assist you in evaluating the variance of a field, the SQL provides the Var() function. Practical Learning: Getting the Average of a Series
Conditions in SQL Aggregate Functions Grouping Values If you have a table or query and want to produce statistics from it, you can create groups of those statistics. For example, if you have employees from different departments, you can get values such as the number of employees in each department, the lowest or highest salary in each department, etc. To visually create groups of statistics, display the query in the Design View:
To let you produce statistics in groups, the SQL provides the GROUP BY expression. The SQL formula to use the GROUP BY clause is: SELECT field(s) FROM table GROUP BY field After the SELECT keyword, create a list of fields. Normally, this section should contain a SQL aggregate function that includes the field on which che statistics will be produced. The GROUP BY expression should be followed by (one of) the extra field(s) from the SELECT statement. Practical Learning: Grouping Values
Statistics from an Expression If none of the above function can by itself produce the value you want, you can formulate an expression. To do this visually, In the Field text box, type an expression of your choice. In its Total field, select Expression. Where is the Condition As seen in previous lessons, you can add a condition by which to select records. To assist with this feature for SQL aggregate functions, the Design View of a query provides a Where option. Of course, first choose the field on which the function will apply. To apply the condition, select the column on which to apply the condition. In its Total field, select Where. In the Criteria box, the a Boolean expression. If you want to apply the condition to the column on which the SQL aggregate function is performed, you must add the same field on another column and crete the condition on it. Practical Learning: Setting Conditions in Statistics
Conditions and Aggregate Functions By default, an aggregate function performs its operation on all values of the field(s) you provide. To support conditions in aggregate functions, the SQL provides the HAVING keyword. To visually set a condition by which to create some statistics, start the query as we saw for the GROUP BY clause. In the bottom side of the Query window, in the column to set the condition, click its Criteria cell and type the desired Boolean expression. The SQL formula to create a condition in a SQL aggregate function is: SELECT field(s) FROM table GROUP BY field HAVING condition The HAVING keyword is used the same way the WHERE operator would be used except that HAVING is used for SQL aggregate functions. Practical Learning: Setting Conditions in Statistics



