MySQL Aggregate Functions – SUM,COUNT, MAX, MIN, AVG

In database management, an aggregate function or aggregation function, according to Wikipedia, is a function where the values of multiple rows are grouped to form a single summary value.

There are different aggregate functions, such as SUM, COUNT, MAX, MIN, AVG, etc. In this tutorial, we’ll cover the most used aggregate function in detail.

Contents

• Introduction
• SUM
• COUNT
• MAX
• MIN
• AVG
• Conclusion

Introduction

Basically an aggragate function performs a calculation on a set of values, and returns a single value.

Consider the table of author that we usually use in our SQL tutorials. For illustration we added salary column.

Let’s see how it works.

SUM

SUM function returns the total sum of a numeric column. Let’s calculate the total amount of salary in our table.

SELECT SUM(salary) as 'Total Amount of Salary' from author;

// you can put it as a tip

The AS command is used to rename a column or table with an alias

// end

Output

Total Amount of Salary

3250

COUNT

COUNT function returns the number of rows that matches a specified criterion, but COUNT ignores null values.

Suppose that you’re the new Database Manager at Codesource and you want to know how many Authors does Codesource have. You can use COUNT for this task.

SELECT COUNT(authorName) as 'Total Authors' from author;

Output

Total Authors

5

MAX

And what about if you want to know what is the highest salary? You can answer this with MAX

MAX returns the maximum value in the expression.

SELECT MAX(salary) as 'Highest Salary' from author;

Output

Highest Salary

2000

Try to find the name of the author who has the highest salary. In future articles we’ll discuss this complex queries.

MIN

And what about if you want to know what is the less salary? You can answer this with MIN

MIN returns the minimum value in the expression.

SELECT MIN(salary) as 'Less Salary' from author

Try to find the name of the author who has the less salary. In future articles we’ll discuss this complex queries.

AVG

AVG returns the average of the values in a group. Let’s find the average of the salaries.

AVG = SUM/COUNT

SELECT AVG(salary) as 'Salary Average' from author;

Output

Salary Average

650

Conclusion

Now you are able to make some basics questions to your database! See you on next articles.

Thanks for reading!