How to Work with Aggregate Functions

  • google plus

In Brief...

Besides column data and calculated fields you can also display the results of aggregate functions on an SQL select statement. An aggregate function derives it value from an aggregation, or group, of values. For example, we might wish to sum the populations of major cities in California or display the average of the population of major cities in Florida.

Take our Introduction to SQL Training course for free.

See the Course Outline and Register

Instructions

To learn work with aggregate functions in a SQL select statement, follow these steps:

  1. You'll need to setup the MySQL database tables. The instructions for the setup can be found in How to add comments in simple SQL selects. Follow steps 1 through 7 before proceeding to the next step.
  2. We turn our attention to working with aggregate functions.
  3. First, let's compute the sum of the populations of major California (state abbreviation = 'CA') cities. Execute the following statement:
    Total population CA Cities
    The sum aggregate function accepts as its argument the column value we wish to sum across the selected rows. The selected rows form the group or aggregation.
  4. Next, we will compute the average population of major Florida (state abbreviation = 'CA') cities. Execute the following statement:
    Average population FL Cities
    The avg aggregate function accepts as its argument the column value we wish to average across the selected rows. As before, the selected rows form the group or aggregation.

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, XML, Windows, Java, Adobe, HTML5, JavaScript, Angular, and much more. Check out our complete course catalog.

Categories

Courses

Author: Stephen Withrow

Stephen has over 30 years' experience in training, development, and consulting in a variety of technology areas including Java, C, C++, XML, JavaScript, AJAX, Tomcat, JBoss, Oracle, and DB2. His background includes design and implementation of business solutions on client/server, Web, and enterprise platforms. Stephen is a published writer in both technical and non-technical endeavors. Stephen received an undergraduate degree in Computer Science and Physics from Florida State University.

Discuss