How to use MySQL Aggregate Functions for Data Science?

by · Published · Updated

MySQL has many aggregate functions that include COUNT, MAX, MIN, SUM, AVG, etc. In this tutorial, we will cover the description and how to use these aggregate functions in our MySQL queries.

Using aggregate functions of MySQL, We can calculate average, sum, minimum, maximum value of the columns of the tables using AVG(), SUM(), MIN() and MAX() functions of MySQL.

List of MySQL Aggregate Functions

  • COUNT() Function
  • AVG() Function
  • SUM() Function
  • MIN() Function
  • MAX() Function

MySQL COUNT() Function

The COUNT () function is used to return the number of records returned by a SELECT statement. When you need to count some records from the database table, at that time we use COUNT function of MySQL. If there are no matching rows, the returned value is 0.

Note : NULL values are not counted.

MySQL COUNT() Function: Syntax

The basic syntax of MySQL COUNT function is :-

SELECT COUNT (columnName)
FROM "tableName"
[WHERE conditions];

MySQL COUNT() Function: Parameter

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Name, Description
tableName ,Name of the table of the database from where you want to retrieve records.
columnName , Column name of the table.
WHERE clause, It is optional. You can add any conditions as per your requirement and these must be matched.
[/table]

MySQL COUNT() Function Query Example

SELECT COUNT(*) FROM employees;

The above example return the count of rows from the table in the database i.e total number of employees.

MySQL SUM() Function

The SUM() function is used to return the total sum of given column returned by a SELECT statement. When you need sum of the column from the database table, at that time we use SUM function of MySQL.

If the return set has no rows, the SUM() function returns NULL.

Note : NULL values are ignored.

MySQL SUM() Function: Syntax

The basic syntax of MySQL SUM function is :-

SELECT SUM (columnName)
FROM "tableName"
[WHERE conditions];

MySQL SUM() Function: Parameter

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Name, Description
tableName ,Name of the table of the database from where you want to retrieve records.
columnName , Column name of the table you want to get total of it.
WHERE clause, It is optional. You can add any conditions as per your requirement and these must be matched.
[/table]

MySQL SUM() Function Query Example

SELECT SUM (salary) AS "totalSalary"  
FROM employees;

The above example return the SUM of rows of the given column name salary from the database table employees.

MySQL AVG() Function

The AVG() function is used to return the average value of given columns in query. When you need to get average of the column from the database table, at that time we use this AVG() function of MySQL.

Note : NULL values are not counted/ignored.

MySQL AVG() Function: Syntax

The basic syntax of MySQL AVG function is :-

SELECT AVG (columnName)
FROM "tableName"
[WHERE conditions];

MySQL AVG() Function: Parameter

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Name, Description
tableName, Name of the table of the database from where you want to retrieve records.
columnName , Column name of the table.
WHERE clause, It is optional. You can add any conditions as per your requirement and these must be matched.
[/table]

MySQL AVG() Function Query Example

SELECT AVG (work_hour) AS "totalHours"
FROM employees WHERE work_hour > 8;

The above example calculate  the average of the given column name work_hour from the database table.

Recommended Post

SELECT statement , SQL INSERT Statement ,  SQL DROP Table , SQL CREATE Table  .

MySQL MAX() Function

The MAX() function is used to return the maximum value of given columns in query or a set of value. If you need maximum value of the column from the database table, at that time we use this MAX() function of MySQL.

MySQL MAX() Function: Syntax

The basic syntax of MySQL MAX function is :-

SELECT MAX(columnName)
FROM "tableName"
[WHERE conditions];

MySQL MAX() Function: Parameter

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Name, Description
tableName, Name of the table of the database from where you want to fetch records.
columnName , Column name of the table.
WHERE clause, It is optional. You can add any conditions as per your requirement and these must be matched.
[/table]

MySQL MAX() Function Query Example

SELECT MAX (salary) AS "highestPaid"
FROM employees;

The above example return the highestPaid salary of the given column name salary from the database table.

MySQL MIN() Function

The MIN() function is used to return the mininum value of given columns in query. If you need minimum value of the column from the database table, at that time we use this MIN(() function of MySQL.

MySQL MIN() Function: Syntax

The basic syntax of MySQL MIN function is :-

 SELECT MIN (column_name)  
 FROM table_name  
 [WHERE conditions];

The basic syntax of MySQL MIN() function is :-

SELECT MIN(columnName)
FROM "tableName"
[WHERE conditions];

MySQL MIN() Function: Parameter

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Name, Description
tableName, Name of the table of the database from where you want to fetch records.
columnName , Column name of the table.
WHERE clause, It is optional. You can add any conditions as per your requirement and these must be matched.
[/table]

MySQL MIN() Function Query Example

SELECT MIN (salary) AS "lowestPaid"
FROM employees;

The above example return the lowestPaid salary of the given column name salary from the database table.

Conclusion

In this MySQL tutorialmines – You have learned MySQL aggregate functions that include AVG, COUNT, SUM, MIN, MAX, with syntax, example.

Tags:

You may also like...