COUNT(), AVG(), and SUM() are three commonly used aggregate functions in MySQL that are used to perform calculations on a set of values.

Points To Remember:

When using these functions, it's important to note that NULL values will be ignored in the calculations.

For the upcoming queries in this blog, let's take a look at the film table first.

SELECT film_id, title, replacement_cost, length
FROM film
LIMIT 5;

Output:

COUNT():

The COUNT() function in MySQL determines the number of rows in a table that matches a specified condition.

The function takes a single argument, a column name, or *, and returns the number of rows that match the condition.

When the column name is specified, the function counts only the non-NULL values in that column.

When the unique character is used, the function counts all rows in the table. It is commonly used to determine the size of a table, to check if a table is empty, or to count the number of occurrences of a specific value in a column.

Syntax:

/*When using a column name*/
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
/*When using a '*'  */
SELECT COUNT(*)
FROM table_name
WHERE condition;

Example:

/*Here we are getting the count of the films where the rating is PG.*/
SELECT count(title)
FROM film
WHERE rating = "PG";

Output:

AVG():

The AVG() function in MySQL calculates the average value of a specific numeric column.

The function takes a single argument, the column name, and returns the average value of that column.

The function ignores any NULL values in the column when making the calculation.

Syntax:

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Example:

/*We are trying to get the average replacement_cost of the films whose rating is PG*/
SELECT AVG(replacement_cost)
FROM film
WHERE rating = "PG";

Output:

SUM():

The SUM() function in MySQL calculates the sum of all values in a specific numeric column.

The function takes a single argument, the column name, and returns the sum of all values in that column.

The function ignores any NULL values in the column when making the calculation.

Syntax:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Example:

/*Here we are finding how much time it will require us to watch all the movies in our DB with a "PG" rating. I assumed that the length is in minutes*/ 
SELECT SUM(LENGTH) 
FROM film 
WHERE rating = "PG";

Output:

In conclusion, the COUNT(), AVG(), and SUM() functions are powerful tools for working with large datasets in MySQL.

These functions allow you to quickly and easily perform calculations on a specific column, whether counting the number of rows, calculating the average value, or determining the total sum.

It's important to remember that these functions require a numeric column as an argument, and the results will ignore any NULL values.

Remember to use these functions in combination with the GROUP BY clause to generate meaningful statistics and insights from your data.

Always remember to use them with the WHERE clause for filtering the data before performing the calculations.

'll meet again tomorrow!

To see more examples, you can always refer w3 schools; it has always been my go-to website to understand things.

References: w3schools.com/mysql/default.asp

*****All the outputs provided are a snippet of the actual result.

10. MySQL: COUNT(), AVG() and SUM() Functions

Points To Remember:

COUNT():

AVG():

SUM():