SQL Aggregate Functions 

Introduction to SQL Aggregate Functions
Overview
An aggregate function is a procedure that considers a group of values and produces a certain value that applies to all of them. The SQL in Microsoft Access supports various categories of aggregate functions.
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
Control  Control Source  Format  Decimal Places  
Text Box  =First([Abbreviation])  
Text Box  =First([StateName])  
Text Box  =First([AreaSqrMiles])  Standard  0  
Text Box  =First([AreaSqrKms])  Standard  0  
Text Box  =First(CDate([AdmissionUnionDate]))  Medium Date  
Text Box  =First([AdmissionUnionOrder])  
Text Box  =First([Capital]) 
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
Control  Control Source  Format  Decimal Places  
Text Box  =Last([Abbreviation])  
Text Box  =Last([StateName])  
Text Box  =Last([AreaSqrMiles])  Standard  0  
Text Box  =Last([AreaSqrKms])  Standard  0  
Text Box  =Last(CDate([AdmissionUnionDate]))  Medium Date  
Text Box  =Last([AdmissionUnionOrder])  
Text Box  =Last([Capital]) 
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
Control  Control Source  Format  Deciml Places  
Text Box  =Min([Abbreviation])  
Text Box  =Min([StateName])  
Text Box  =Min([AreaSqrMiles])  Standard  0  
Text Box  =Min([AreaSqrKms])  Standard  0  
Text Box  =Min(CDate([AdmissionUnionDate]))  Medium Date  
Text Box  =Min([AdmissionUnionOrder])  
Text Box  =Min([Capital]) 
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
Control  Control Source  Format  Decimal Places  
Text Box  =Max([Abbreviation])  
Text Box  =Max([StateName])  
Text Box  =Max([AreaSqrMiles])  Standard  0  
Text Box  =Max([AreaSqrKms])  Standard  0  
Text Box  =Max(CDate([AdmissionUnionDate]))  Medium Date  
Text Box  =Max([AdmissionUnionOrder])  
Text Box  =Max([Capital]) 
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
SELECT States.Region, Sum(States.AreaSqrMiles) AS [Total Region Area (in Square Miles)] FROM States GROUP BY States.Region;
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
SELECT States.Region, Count(States.Region) AS CountOfRegion FROM States WHERE (((States.AreaSqrMiles)>=50000)) GROUP BY States.Region;
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
SELECT States.Region, Count(States.Region) AS CountOfRegion FROM States GROUP BY States.Region HAVING (((States.Region) Like "*south*"));


Previous  Copyright © 20002022, FunctionX, Inc.  Next 
