Using aggregate functions

In the previous lesson we looked at creating calculated fields that performed a math calculation on each field value. We can perform calculations on the entire field column using SQL’s aggregate functions. This will allow us to look at the data as a whole and return back calculations such as the average value, the number of values, the total sum of all the values and more.

AVG() function

The AVG() function returns the average value (mean) of the entire data set. Below is an example of finding the average book price.

SELECT AVG(price)
FROM books;

COUNT() function

The COUNT() function returns the total number of records. Below is an example of finding the total number of books over the price of $10 Since the primary key field is required in each record we will use that field to count.

SELECT COUNT(book_id)
FROM books
WHERE price > 10;

FIRST() and LAST()

The FIRST() and LAST() functions simply return the first and last field value of the query, respectfully. Below is an example of using both FIRST() and LAST() on the same query.

SELECT FIRST(price), LAST(price)
FROM books;

MAX() and MIN()

The MAX() and MIN() functions simply return the highest and lowest field value of the query, respectfully. Below is an example of using both MAX() and MIN().

SELECT MAX(price), MIN(price)
FROM books;

SUM()

The SUM() function returns the value of all fields added up on a query. Below is an example of using the SUM() function.

SELECT SUM(price)
FROM books;

Lesson Activities

  1. Using the book table, add up the total cost of all the books.
    Click to see code
    SELECT SUM(price)
    FROM books;
  2. Using the book table, find the average price of all the books.
    Click to see code
    SELECT AVG(price)
    FROM books;

Questions