How to MySQL- Aggregate functions

MySQL has many built-in functions for performing calculations on data. There are two types of function, Aggregate, and scalar. An aggregate function performs a calculation on a set of values and returns a single value.
MySQL provides many aggregate functions that include.

  • COUNT() – Returns the number of rows.
  • SUM() – Returns the sum.
  • AVG() – Returns the average value.
  • MIN() – Returns the smallest value.
  • MAX() – Returns the largest value.
  • FIRST – Returns the first value.
  • LAST – Returns the last value.

Let’s learn each of function with example.

 

COUNT

MySQL count() function is used to return the count of an expression.

Syntax
     SELECT COUNT(column_name) FROM table_name;

 

Example:

Consider a table named “contacts”, having the following contact data.

Contacts Table

MySQL Aggregate Functions

 

The following SQL statement counts the total number of email addresses in the table contacts and will return it.

Sample code
     SELECT COUNT(email) FROM contacts;
Output

MySQL Aggregate functions

 

SUM

The MySQL sum() function is used to return the total summed value of an expression.

Syntax
     SELECT SUM(column_name) FROM table_name;

 

Example:
Consider the table “contacts” below, which have some updated data in it.

Contacts Data

MySQL Aggregate Functions

 

The following SQL statement returns summed value of all salaries in the table contacts.

SUM Example
     SELECT SUM(salary) FROM contacts;
Output

MySQL Aggregate Functions

 

AVG

The MySQL Avg() function is used to return the average value of an expression.

Syntax
     SELECT AVG(column_name) FROM table_name;

 

Example:

The following SQL statement returns average value of all salaries in the table contacts.

AVG Example
     SELECT AVG(salary) FROM contacts;
Output

MySQL Aggregate Functions

 

MIN

The MySQL min() function is used to return the minimum value from specified column of the table.

Syntax
     SELECT MIN(column_name) FROM table_name;

 

Example:

The following SQL statement returns minimum value from salary column.

MIN Example
     SELECT MIN(salary) FROM contacts;
Output

MySQL Aggregate Functions

 

MAX

The MySQL max() function is used to return the largest value from specified column of the table.

Syntax
     SELECT MAX(column_name) FROM table_name;

 

Example:

The following SQL statement returns the largest value from salary column.

MAX Example
     SELECT MAX(salary) FROM contacts;
Output

MySQL Aggregate Functions

 

FIRST

The FIRST() function returns the first value of the selected column. Here we use limit clause to select first record or more

Syntax
     SELECT column_name FROM table_name  
     LIMIT 1;

 

Example:

The following SQL statement returns the first value from firstname column.

FIRST Example
     SELECT firstname FROM contacts  
     LIMIT 1;
Output

MySQL Aggregate Functions

 

LAST

The LAST() function returns the last value of the selected column. Here we use limit clause to select first record or more

Syntax
     SELECT column_name FROM table_name  
     ORDER BY column_name DESC 
     LIMIT 1;

 

Example:

The following SQL statement returns the last value from firstname column of contact table.

LAST Example
     SELECT firstname FROM contacts  
     ORDER BY id DESC 
     LIMIT 1;
Output

MySQL Aggregate Functions

Anwar Yakkiparamban

Anwar Yakkiparamban is the founder of Lauyou Learning. Prior to Lauyou learning, Anwar worked at ARD Engineering & Development, Qatar. He holds bachelor degree in Electronics and Communication Engineering from Govt. Engineering College Idukki.

You may also like...