5) SQL Commands for Querying Databases Lesson

SUM / COUNT / AVG

5 min to complete · By Ryan Desmond

In MySQL, and all other common databases, there are a number of intuitive helper functions such as SUM(), COUNT(), and AVG().

These functions do exactly as they say. They find the SUM of a column, the COUNT of a column, or the average (AVG) of a column. This lesson defines and provides the syntax for these functions.

Colorful illustration of a light bulb

Note: This lesson uses the MySQL Sakila demo database. If you haven't already, follow the steps to install the Sakila DB, then head back here to complete these queries.

SQL Sum

SQL SUM adds up all the values of a given column to produce the total sum.

For example, find the sum of all payments in the payment table.

SELECT SUM(amount) FROM payment;
Sum of the amount column in the payment table

After running this query, you might notice that the name of the column in the result set is SUM(amount). That's not a great column name.

Fortunately, you can rename it very easily using the AS keyword. The AS keyword allows you to rename the column in the result set. 

Sum amount as total revenue 2005 in the payment table

SQL Sum Example with Conditions

Here's another SQLSUM query that specifically checks the total revenue in the year 2005:

SELECT SUM(amount) AS total_revenue_2005 
FROM payment 
WHERE payment_date > "2005-01-01" 
AND payment_date < "2006-01-01"
Sum amount as total revenue 2005 in the payment table in 2005

SQL Count

SQL COUNT allows you to count the total number of records in a given query.

For example, maybe you need to see how many payments you received in 2005:

SELECT COUNT(amount) AS num_payment_2005 
FROM payment 
WHERE payment_date > "2005-01-01" 
AND payment_date < "2006-01-01"
Count amount in year 2005 of payment table

How many customers do you have in the customer table? Take a look!

SELECT COUNT(customer_id) AS customers
FROM customer;
Count customer_id in customers table

SQL Average

The SQL Average function (AVG) allows you to find the average of a given column - or field - in the result set.

For instance, using the same dataset, determine what your average payment amount is.

SELECT AVG(amount) AS avg_payment
FROM payment;
Avg of amount as avg_payment in payment table

Summary: SQL Sum, SQL Count, SQL Average

  • The SQL SUM() function takes the sum of a numeric column
  • The SQL COUNT() function counts the number of records in a column
  • The SQL AVG() function takes the average of a numeric column
  • The AS keyword allows you to choose the name of the column created with the results of the function

Syntax

Here's the syntax for the SQL Sum, SQL Count, and SQL Average functions, where you can substitute the variables in < > with your values.

-- SUM function
SELECT SUM(<YOUR_COLUMNS>)
FROM <YOUR_TABLE>

-- COUNT function
SELECT COUNT(<YOUR_COLUMNS>)
FROM <YOUR_TABLE>

-- AVG function with optional AS command
SELECT AVG(<YOUR_COLUMNS>) AS <YOUR_NEW_COLUMN_NAME>
FROM <YOUR_TABLE>

Wow, and just like that, you already know three functions! Are you ready for some more? Next up are MIN() and MAX().