Aggregate Functions in MySQL

Aggregate Functions

Aggregate Functions in MySQL
Aggregate Functions in MySQL


Learn Aggregate Functions in MySQL

Till now you have learnt to work with functions that operate on individual rows in a table e.g., if you use Round( ) function then it will round off values from each row of the table.


MySQL also supports and provides group functions or aggregate functions. As you can make out that the group functions or aggregate functions work upon groups of rows, rather than on single rows. That is why, these functions are sometimes also called multiple row functions.

Many group functions accept the following options :


DISTINCT

This option causes a group function to consider only distinct values of the argument expression.


ALL


This option causes a group function to consider all values including all duplicates.


The usage of these options will become clear with the coverage of examples in this section.
All the examples that we’ll be using here, shall be based upon following table empl.

Table Database table empl

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
8369SMITHCLERK89021990-12-18800.00NULL20
8499ANYASALESMAN86981991-02-201600.00300.0030
8521SETHSALESMAN86981991-02-221250.00500.0030
8566MAHADEVANMANAGER88391991-04-022985.00NULL20
8654MOMINSALESMAN86981991-09-281250.001400.0030
8698BINAMANAGER88391991-05-012850.00NULL30
8839AMIRPRESIDENTNULL1991-11-185000.00NULL10
8844KULDEEPSALESMAN86981991-09-081500.000.0030
8882SHIAVNSHMANAGER88391991-06-092450.00NULL10
8886ANOOPCLERK88881993-01-121100.00NULL20
8888SCOTTANALYST85661992-12-093000.00NULL20
8900JATINCLERK86981991-12-03950.00NULL30
8902FAKIRANALYST85661991-12-031300.00NULL20
8934MITACLERK88821992-01-231300.00NULL10
database table empl

1. AVG


This function computes the average of given data.


Syntax :-

AVG([DISTINCT / ALL] n)
  • Returns average value of parameter(s) n.


Argument type : Numeric Return value : Numeric

Example 1. Calculate average salary of all employees listed in table empl.


Solution.

mysql> SELECT AVG(sal) "Sverage"
             from empl ;
Average
2073.928571
Output

1 row in set (0.01 sec)

2. COUNT


This function counts the number of rows in a given column or expression.


Syntax :

COUNT({ *[DISTINCT / ALL] expr})
  • Returns the number of rows in the query.
  • if you specify argument expr, this function returns rows where expr is not null. You can count either all rows, or only distinct values of expr.
  • If you specify the asterisk (*), this function returns all rows, including duplicates and nulls.

Argument type : Numeric Return value : Numeric

Example 2. Count number of records in table empl.


Solution.

mysql> SELECT COUNT(*) "Total"
             From empl ;
Total
14
Output

1 row in set (0.01 sec)

Example 3. Count number of jobs in table empl.


Solution.

mysql> SELECT COUNT(job) "Job Count"
             From empl ;
Job Count
14
Output

1 row in set (0.01 sec)

Example 4. How many distinct jobs are listed in table empl ?


Solution.

mysql> SELECT COUNT(DISTINCT job) "Distinct Jobs"
             From empl ;
Distinct Jobs
15
Output

1 row in set (0.01 sec)

3. MAX


This function returns the maximum value from a given column or expression.


Syntax :

MAX( [DISTINCT / ALL] expr)

Returns maximum value of argument expr.

Argument type : Numeric Return value : Numeric

Example 5. Display maximum salary from table empl.


Solution.

mysql> SELECT MAX(sal) "Maximum Salary"
             From empl ;
Maximum Salary
5000.00
Output

1 row in set (0.01 sec)

4. MIN


This function returns the minimum value from a given column or expression.


Syntax :

MIN( [SIATINCT / ALL] expr)
  • Returns minimum value of expr.

Argument type : Numeric Return value : Numeric


Example 6. Display the joining date of seniormost employee.


Solution.

mysql> SELECT MIN(hiredate) "Minimum Hire Date"
             From empl ;
Minimum Hire Date
1990-12-18
Output

1 row in set (0.01 sec)

5. SUM


This function returns the sum of values in given column or expression.


Syntax :

SUM( [DISTINCT / ALL] n)
  • Returns sum of values of n.

Argument type : Numeric Return value : Numeric

Example 7. Display total salary of all employees listed in table empl.


Solution.

mysql> SELECT SUM(sal) "Total Salary"
             From empl ;
Total Salary
29035.00
Output

1 row in set (0.01 sec)

Examples :

1. To calculate the total gross for employees of grade ‘E2’,
the command is :

SELECT SUM(gross) FROM employee
WHERE grade = 'E2' ;

2. To display the average gross of employees with grades ‘E1’ or ‘E2’, the command used is :

SELECT AVG(gross) FROM employee
WHERE (grade = 'E1' OR grade = 'E2') ;

3. To count the number of employees in employee table, the SQL command is :

SELECT COUNT(*)
FROM employee ;

4. To count the number of cities, the different members belong to, you use the following command :

SELECT COUNT(DISTINCT city) FROM members ;

Here the DISTINCT keyword ensures that multiple entries of the same city are ignored. The * is the only argument that includes NULLs when it is used only with COUNT, functions other than COUNT disregard NULLs in any case.


If you want to count the entries including repeats, the keyword ALL is used. The following command will COUNT the number of non NULL city fields in the member table

Read More :-

1. MySQL Functions Example

2. Python Dictionaries Tutorial

3. Python Tuples Tutorial

4. Python Lists Tutorial

5. MySQL Aggregate Functions – MySQL Tutorial

Python Tutorial 2021

FAQ :-

Q 1. What are aggregate functions explain with examples?

Ans. SQL provides various aggregate functions which can summarize data of given table. This function is used to calculate number of rows in a table selected by query. COUNT returns the number of rows in the table when the column value is not NULL.

Q 2. What are the 6 aggregate functions of SQL?

Ans.

SQL Aggregate Functions

  • AVG – calculates the average of a set of values.
  • COUNT – counts rows in a specified table or view.
  • MIN – gets the minimum value in a set of values.
  • MAX – gets the maximum value in a set of values.
  • SUM – calculates the sum of values.

Q 3. What is an example of an aggregate?

Ans. An aggregate is a collection of people who happen to be at the same place at the same time but who have no other connection to one another. Example: The people gathered in a restaurant on a particular evening are an example of an aggregate, not a group.

Q 4. Is sum an aggregate function?

Ans. The SQL Server SUM() function is an aggregate function that calculates the sum of all or distinct values in an expression. In this syntax: ALL instructs the SUM() function to return the sum of all values including duplicates. ALL is used by default.

Q 5. What is a aggregate symbol?

Ans.  Aggregate. The aggregate is indicated by the symbol M if the candidate has met with all the requirements for an endorsement for university admission, and the symbol S in all other cases.

Aggregate Functions In MySQL video :-

Aggregate Functions In MySQL