How to Use the SUM Function in MySQL

MySQL provides a number of aggregate functions for quickly compiling data from your tables. These aggregate functions work within SELECT queries, and their results return in special columns. The aggregate functions are fast--much faster than it would be for you to return all the rows and calculate them yourself. This can mean the difference between a 2-second query with the aggregate function and a 2-minute query doing the calculation yourself in application code.

Instructions

Use the SUM Function in MySQL

1. Use the SUM aggregate function. SUM will add all of the selected columns together.

2. Notice how, in this example, you'll sum all of the negative balances to calculate the total amount owed to you by your customers. The result returns in a column called SUM(balance). Example:
SELECT SUM(balance) FROM customers WHERE balance < 0.0;

Use the AVG Function in MySQL

1. Use the AVG aggregate function. This aggregate is similar to SUM. However, instead of adding the columns, it adds them and then divides by the total number of columns, giving you an average of the column values.

2. Use AVG in much the same way SUM is used. Here, the example calculates the average amount of money owed by customers. Results return in a column called AVG(balance). Example:
SELECT AVG(balance) FROM customers WHERE balance < 0.0;

Use the COUNT Function in MySQL

1. Use the COUNT aggregate function. This function is slightly different than SUM or AVG. It returns the number of rows returned, which can be useful in a number of situations--for example, to see the number of customers who owe money.

2. See how this example counts the customers who owe money and returns the result in a column called COUNT(*). Example:
SELECT COUNT(*) FROM customers WHERE balance < 0.0;

Use the DISTINCT COUNT Function in MySQL

1. Use the DISTINCT COUNT aggregate function. The function can be used to return the number of distinct rows, meaning those rows with differing values. Theoretically, you might have a lot of customers with the same phone number. To get the number of households your customers live in, you can use this type of query.

2. Use the following example to return the number of customers with distinct phone numbers. Customers with the same phone number will not be counted. The results are returned in a column called COUNT(DISTINCT ph_number). Example:
SELECT COUNT(DISTINCT ph_number) FROM customers;

Tips & Warnings

• An aggregate function is one that performs a calculation on a set of values as opposed to on a single value. In a MySQL database, such functions are made on many rows of data.