EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Aggregate Function
 

MySQL Aggregate Function

Priya Pedamkar
Article byPriya Pedamkar

Updated May 15, 2023

MySQL Aggregate Function

 

 

Introduction to MySQL Aggregate Function

Mainly in the data query language, we use these aggregated functions with SELECT statements. An aggregate function performs a calculation on multiple values and returns a single value like the sum of all values, maximum, and minimum among certain groups of values.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Example: If we have to calculate the total sales of a product in a month, then we have to use the ‘SUM’ function to add up all the sales values. Similarly, using the ‘MAX’ and ‘MIN’ functions, we can get the highest and lowest sale of that particular month. Aggregate functions ignore NULL values except for the ‘COUNT’ function. The count function returns the total number of observations. The HAVING clause is used along with GROU BY for filtering queries using aggregate values.

Top 11 MySQL Aggregate Function

Here are some MySQL aggregate functions which are explained below:

  1. AVG() Function
  2. COUNT() Function
  3. Sum() Function
  4. Max() Function
  5. MIN() Function
  6. DISTINCT() Function
  7. GROUP_CONCAT() Function
  8. VAR() Function
  9. STDEV() Function
  10. BIT_AND() Function
  11. BIT_OR() Function
Section Name Marks
Sec-A Stewart 90
Sec-B Vince 86
Sec-C John 94
Sec-A Michelle 78
Sec-C Robin 60
Sec-A Sara 86
Sec-B Peter 92
Sec-C Ian 89
Sec-A David 76

1. AVG() Function

This is an average function. Its function calculates the average value for a set of values. It ignores null values in the calculation.

For getting average marks from all the students.

Query:

SELECT AVG(marks) AS avg_marks FROM student;

Output:

MySQL Aggregate Function -2

If we want the average mark of students for each section, we can use AVG() with the GROUP BY function.

Query:

SELECT section, AVG(marks) AS avg_marks FROM student GROUP BY section;

Output:

MySQL Aggregate Function -3

2. COUNT() Function

The COUNT() function returns the value of the total number of observations/total number of values in a set of values.

If we perform this function in the above set of examples for getting the number of students,

Query:

SELECT COUNT(name) AS total_students FROM student;

Output:

count

To get the student count in each section,

Query:

SELECT section, COUNT(name) AS total_students FROM student GROUP BY section;

Output:

student count

3. Sum() Function

The SUM() function returns the sum of all values in a set. To get the sum of marks of all the students,

Query:

SELECT SUM(marks) AS total_marks FROM student;

Output:

sum function

The sum of marks of all students section-wise,

Query:

SELECT section, SUM(marks) AS total_marks FROM student GROUP BY section;

Output:

sum student

4. Max() Function

The max() function returns the maximum value in a set of values. To find the highest scorer in the exam from the student database, the below query can give us the desired output:

Query:

SELECT name, MAX(mark) AS highest_mark FROM student;

Output:

Max() function

In the same process, we can determine the maximum mark secured from each section.

Query:

SELECT section, name, MAX(mark) AS highest_mark FROM student GROUP BY section;

5. MIN() Function

MIN() function returns the lowest value from the set of values. This doesn’t consider the null values. Lowest scorer among the students,

Query:

SELECT name, MIN(mark) AS lowest_mark FROM student;

Output:
MIN() function

This also can be used with the GROUP BY function.

6. DISTINCT() Function

This function is used mainly with the COUNT function to get the number of unique values in the set of values. We can also use this DISTINCT function to get the unique values.

Query:

SELECT DISTINCT (section) FROM student;

Output:

MySQL Aggregate Function -4

The query for using DISTINCT with COUNT() function.

Query:

SELECT COUNT(DISTINCT(section)) FROM student;

Output:

MySQL Aggregate Function -5

7. GROUP_CONCAT() Function

This is used to concat all the string values of one attribute and merge them into one index.

Query:

SELECT GROUP_CONCAT(name SEPARATOR ';') FROM student;

Output:

MySQL Aggregate Function -6

Like this GROUP_CONCAT(), we also use another function CONCAT(), which merges two sets of string values into a new column.

Example:

MySQL Aggregate Function -7

If, in this name database, we use the function CONCAT(),

Query:

SELECT first_name, last_name, CONCAT(first_name,’ ‘,last_name) as full_name FROM name;

Output:

MySQL Aggregate Function -8

8. VAR() Function

This variance function returns the population standard variance of the specified column.

Variance is a measurement of the spread between the numbers in a dataset. It is calculated by how far each number is from the mean and, therefore, from each number of the set.

Variance:

Variance

Query:

SELECT VAR(mark) AS variance FROM student;

Output:

Variance output

9. STDEV() Function

Standard deviation is the measure of the amount of variation or dispersion of a set of values. This expresses how much the group members differ from the group’s mean value. This is calculated by taking the square root of the variance.

Variance square root

This function returns the population standard deviation of the specified column.

Query:

SELECT STDEV(mark) AS std_deviation FROM student;

Output:

standard dev

10. BIT_AND() Function

This function returns the bit-wise AND of specified rows. This returns the same data type, whichever is passed through argument.

If every row has 1 in the bit position, only it will return 1. Otherwise, it returns 0.

bit-wise AND

Query:

SELECT BIT_AND( CAST(row_value VariableBIT) ) FROM student.list('0001,0111,0100,0011');

Output:

bit_and

11. BIT_OR() Function

Returns the bit-wise OR of the specified expression for each group of rows.

Query:

SELECT BIT_OR( CAST(row_value AS VariableBIT) )
FROM student.list('0001,0111,0100,0011');

The result of 0111 is determined as follows:

  • A bitwise OR is performed between rows 1 (0001) and 2 (0111), resulting in 0111.
  • Bitwise OR is performed between the result from the previous comparison (0111) and row 3 (0100), resulting in 0111.
  • A bitwise OR is performed between the result from the previous comparison (0111) and row 4 (0011), resulting in 0111.

Conclusion

Those aggregated functions are essential from the analysis perspective and in the extraction process. Specifically, aggregated functions play significant roles when we give a condition in a query or while using a Windows function. Some functions like LIMIT, RANK, etc., and GROUP BY clause always comes with aggregated functions.

Recommended Articles

We hope that this EDUCBA information on “MySQL Aggregate Function” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL vs SQLite
  2. Insert Command in MySQL
  3. SELECT in MySQL
  4. MySQL BETWEEN

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - ENROLL NOW