MySQL Aggregate Functions

Introduction

The aggregate function in MySQL is used to calculate several values and return the result as a single value, such as the average of all values, the sum of all values, and the maximum and minimum value among groupings of values. In data query languages, we generally utilize aggregate functions with SELECT statements.

MySQL count()

The count() method in MySQL is used to return the number of times an expression has been utilized. It allows us to count all or just some of the table’s rows that match a given criterion. It’s a type of aggregate function with a BIGINT return type. If no matching rows are found, this function returns 0.

Syntax:

SELECT COUNT (aggregate_expression)

FROM table_name

[WHERE conditions];

Example and Output:

MySQL sum()

The sum() method in MySQL is used to return an expression’s total summed value. If there are no rows in the result set, it returns NULL. In MySQL.

Syntax:

SELECT SUM(aggregate_expression)

FROM tables

[WHERE conditions];

Example and Output:

MySQL avg()

The aggregate method avg() in MySQL is used to return the average value of an expression across several entries.

Syntax:

SELECT AVG(aggregate_expression)

FROM tables

[WHERE conditions];

Example and Output:

MySQL min()

In MySQL, the MIN() method is used to return the table’s minimal value from a set of values. It’s an aggregate function that comes in handy when we need to find the smallest number, choose the cheapest product, and so on.

Syntax:

SELECT MIN ( DISTINCT aggregate_expression)

FROM table_name(s)

[WHERE conditions];

Example and Output:

MySQL max()

The MAX() method in MySQL is used to return the highest value in a set of expression values. When we need to locate the greatest number, choose the most expensive goods, or acquire the largest payment to the customer from your table, this aggregate function comes in handy.

Syntax:

SELECT MAX(DISTINCT aggregate_expression)

FROM table_name(s)

[WHERE conditions];

Example and Output:

MySQL first()

The first function in MySQL is used to return the first value of a column. To choose the first or more records, we utilize the limit clause.

Syntax:

SELECT column_name

FROM table_name

LIMIT 1;

Example and Output:

MySQL last()

The last function in MySQL is used to return the last value of a column.

Syntax:

SELECT column_name

FROM table_name

ORDER BY column_name DESC

LIMIT 1;

Example and Output:

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.