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

1. Start Microsoft Access
2. In the list of files, click StatesStatistics3 from Lesson 40
3. On the Ribbon, click Create and, in the Forms section, click Form Design
4. In the Property Sheet, change the following characteristics:
   Record Source: States
   Caption: States Statistics
   Default View: Continuous Forms
   Auto Center: Yes
   Record Selectors: No
   Navigation Buttons: No
   Dividing Lines: Yes
5. Right-click the body of the form and click Form Header/Footer
6. Save the form as Statistics Summary
7. In the Tools section of the Design tab of the Ribbon, click Add Existing Fields
8. Double-click each item from the Field List to add it to the form
9. Complete the design of the form as follows:

   

10. Save the form
11. On the Ribbon, click Create and click Query Design
12. In the Show Table dialog box, double-click States and click Close
13. In the top list of items, double-click StateName

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:

• In the Show/Hide section of the Ribbon, click the Totals button
• Right-click the bottom section of the window and click Totals

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

• In the Show/Hide section of the Ribbon, click the Totals button

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 function-name(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

1. In the bottom side of the window, replace Group By with Count

   

2. To see the result, on the Ribbon, click the Run button

   

3. Right-click the title bar of the window and click SQL View
4. Right-click the title bar of the window and click Design View
5. Display the form in the background
6. In the Controls section of the Design tab of the Ribbon, click the Text Box and click below the Form Header bar
7. In the Property Sheet, change the Control Source of the text box to =Count([StateName])
8. Change the caption of the accompanying label to States:
9. Position the text box and its label to the left side:

   

10. To preview the form, switch it to the Form View
11. Save the form
12. Right-click its title bar and click Design View to switch it back

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 non-numeric field, you will receive an error. Here is an example:

Practical Learning: Getting a Statistical Sum

1. In the Controls section of the Design tab of the Ribbon, click the Text Box and click an area in the Form Footer section
2. In the Property Sheet, change the following properties of the text box:
   Control Source: =Sum(Nz([AreaSqrMiles]))
   Format: Standard
   Decimal Places: 0
3. Change the caption of the accompanying label to Total:
4. On the form, right-click the new text box and click Copy
5. Right-click somewhere in the Form Footer section and click Paste
6. Delete the accompanying label of the new text box
7. In the Property Sheet, change the Control Source of the new text box to =Sum(Nz([AreaSqrKms]))
8. Position the label and the new text boxes as follows:

   

9. To preview the form, switch it to the Form View:

   

10. Save the form and switch it back to the Design View

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 number-based column. Here is an example:

Practical Learning: Getting the Average of a Series

1. On the form, select the text boxes in the Form Footer section
2. Right-click the selection and click Copy
3. Press Ctrl + V to paste
4. Change the caption of the new label to Average
5. In the Property Sheet, change the Control Source of the left new text box to =Avg(Nz([AreaSqrMiles]))
6. Change the Control Source of the right new text box to =Avg(Nz([AreaSqrKms]))

   

7. To preview the form, switch it to the Form View

   

8. Save the form and switch it back to the Design View

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

1. From the Controls section of the Ribbon, add 7 Text Boxes to the form
2. Set the caption of the label of the first text box as First
3. Delete the labels of the other text boxes
4. Complete the design of the form as follows:

   

   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])

5. To preview the form, switch it to the Form View

   

6. Switch the form to the Design View and save it

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

1. From the Controls section of the Ribbon, add 7 Text Boxes to the form
2. Set the caption of the label of the first text box as Last
3. Delete the labels of the other text boxes
4. Complete the design of the form as follows:

   

   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])

5. To preview the form, switch it to the Form View

   

6. Switch the form to the Design View and save it

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:

• If the field uses text, the database engine refers to the rules of the alphabet of the language of the computer
• If the field is number-based, the database engine uses the incrementing logic, considering, negative, 0, and positive values
• If the field uses date, times, or a combination of date and time values, the database engine refers to the rules of chronological orders of date and time values
• If the field uses Boolean values, if there is a False or an unchecked box, the function returns -1

Practical Learning: Getting the Lowest Value of a Field

1. From the Controls section of the Ribbon, add 7 Text Boxes to the form
2. Set the caption of the label of the first text box as Minimum
3. Delete the labels of the other text boxes
4. Complete the design of the form as follows:

   

   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])

5. To preview the form, switch it to the Form View
6. Switch the form to the Design View and save it

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

1. From the Controls section of the Ribbon, add 7 Text Boxes to the form
2. Set the caption of the label of the first text box as Maximum
3. Delete the labels of the other text boxes
4. Complete the design of the form as follows:

   

   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])

5. To preview the form, switch it to the Form View

   

6. Switch the form to the Design View and save it

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 number-based field.

Practical Learning: Getting the  Standard Deviation

1. In the Controls section of the Design tab of the Ribbon, click the Text Box and click somewhere in the Form Footer section
2. In the Property Sheet, change the following properties of the text box:
   Control Source: =StDev(Nz([AreaSqrMiles]))
   Format: Standard
   Decimal Places: 0
3. Change the caption of the accompanying label to Standard Deviation::
4. On the form, right-click the new text box and click Copy
5. Right-click somewhere in the Form Footer section and click Paste
6. Delete the accompanying label of the new text box
7. In the Property Sheet, change the Control Source of the new text box to =StDev(Nz([AreaSqrMiles]))
8. Position the label and the new text boxes as follows:

   

9. To preview the form, switch it to the Form View
10. Save the form and switch it back to the Design View

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

1. On the form, select the Standard Deviation text boxes in the Form Footer section
2. Right-click the selection and click Copy
3. Press Ctrl + V to paste
4. Change the caption of the new label to Variance
5. In the Property Sheet, change the Control Source of the left new text box to =Var(Nz([AreaSqrMiles]))
6. Change the Control Source of the right new text box to =Var(Nz([AreaSqrKms]))

   

7. To preview the form, switch it to the Form View

   

8. Save and close the form

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:

• Select a field that has repeating values. Leave its Total row to Group By
• Select an additional field that holds the values for the statistics. In the bottom side of the window and in the Total row, select the desired function

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

1. In the bottom side of the Query window, replace StateName with Region
2. Set its Total cell to Group By
3. In the top side of the window, double-click StateName to add that field
4. Set its Total cell to Count

   

5. To see the result, on the Ribbon, click the Run button

   

6. Right-click the title bar of the window and click SQL View
7. Change the code as follows:

   SELECT States.Region,
   Sum(States.AreaSqrMiles) AS [Total Region Area (in Square Miles)]
   FROM States
   GROUP BY States.Region;

8. To see the result, right-click the title bar of the window and click Datasheet View

   

9. Right-click the title bar of the window and click Design View

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

1. In the bottom side of the window, replace the content of the second column header with Region
2. Set its Total cell to Count
3. In the list of items, double-click AreaSqrMiles to add it to the third column
4. In its Total combo box, select Where
5. Click Criteria cell and type >=50000 (the goal to count the number of states in each region only if a state has an area higher than 50000 square miles)

   

6. To see the result, on the Ribbon, click the Run button

   

7. Right-click the title bar of the window and click SQL View

   SELECT States.Region, Count(States.Region) AS CountOfRegion
   FROM States
   WHERE (((States.AreaSqrMiles)>=50000))
   GROUP BY States.Region;

8. Right-click the title bar of the window and click Design View
9. In the bottom side of the window, right-click each column header and click Cut to delete all fields

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

1. In the list of items, double-click Region
2. In the bottom side of the window, click the Criteria cell for the Region column and type *south* (the intention is to consider only regions that have the word SOUTH in their name)
3. In the list of items, double-click Region again
4. Set its Total to Count

   

5. To see the result, on the Ribbon, click the Run button

   

6. Right-click the title bar of the window and click SQL View

   SELECT States.Region, Count(States.Region) AS CountOfRegion
   FROM States
   GROUP BY States.Region
   HAVING (((States.Region) Like "*south*"));

7. Close Microsoft Access
8. When asked whether you want to save the query, click No

Previous	Copyright © 2005-2021, FunctionX, Inc.	Next