Updated on 30 Jul 2019 | 4 Min Read
By
 

For MySQL interview questions searchers; this article is going to be extremely helpful as we will discuss one of the most important concepts of MySQL here – the MySQL aggregate functions. Here, we are going to summarize the introduction, functionality, advantages, and other useful insights of MySQL aggregate function for your thorough acknowledgment. This could be considered as an intermediate MySQL tutorial that MySQL aspiring developers must take a peak on to understand all the aspects of aggregate functions available in MySQL.

Let's Have an Introduction to MySQL's Aggregate Functions

In MySQL, the aggregate function allows users to perform a calculation on a set of values to achieve a single scalar value. An aggregate function is often used with the HAVING and GROUP BY clauses present in the SELECT statement. These functions are deterministic in nature because they return the exact same value for a set of properties each time they are called. The aggregate functions can only be used as expressions in the mentioned two scenarios.

Also Read: What's the difference between Oracle and MySQL
  • A HAVING clause.
  • SELECT statement's select list. It could be either an outer query or a subquery.

To call a MySQL aggregate function, users require to implement the following syntax.
aggregate_function (DISTINCT | ALL expression)

Debriefing the above syntax, we have to first specify an aggregate function that we desire to use. Thereafter, we have to put the ALL or DISTINCT modifier followed by the expression inside parentheses. With the use of a DISTINCT modifier, the aggregate function will consider only unique values, ignoring the duplicate values. Whereas, the ALL modifier will calculate or evaluate all the values present, including the duplicate values.

Commonly Used MySQL Aggregate Functions

Below mentioned is a list of the most developer used MySQL aggregate function, which we are going to thoroughly describe further down. The MySQL aggregate functions with examples are extremely handpicked by interviewers as vital Advanced MySQL interview questions

  • COUNT
  • AVG
  • MAX
  • MIN
  • SUM

1. COUNT

This aggregate function is typically used by developers to get the exact number of rows in a particular column. Here are the different functions with their respected syntax to use the COUNT aggregate function. To count all the rows present in a dataset:

You can also read about Joins in MySQL

SELECT COUNT(*) FROM products;

Note: Here we have taken products; as our example dataset. The COUNT(*) is used to select all the rows. It has the same effect as COUNT(1).This one will provide a count of all the rows in which the column high is not null.

SELECT COUNT(high) FROM products;

2. AVG

The aggregate function AVG calculates the accurate average of a selected dataset's group of values. It can only be used with numerical columns. The function ignores nulls completely.

Syntax

SELECT AVG(high) FROM products;

Note: If you want to read MongoDB Interview Questions then you can click here.

3. MAX

The MySQL aggregate function MAX returns the highest values present in a particular column of the dataset. Similar to COUNT, they can also be used on non-numerical columns. It will return the highest number, non-numerical value alphabetically, or the latest date.

Syntax

SELECT MAX(volume) AS max_volume FROM historical_stock_price

4. MIN

This one works opposite to the MAX aggregate function. The MIN function returns the lowest values present in a particular column.

Syntax

SELECT MIN(volume) AS max_volume FROM historical_stock_price

5. SUM

This aggregate function returns the sum of all the values in the specified column min table. It works on numeric fields.

Syntax

SELECT SUM(salary) AS totalSalary FROM employee

If you wish to practice a number of expert-selected SQL interview questions with suggested answers, visit our website Best Interview Questions today. Our web portal offers the most interview asked technical questions and answers for your practice.