Sql Aggregate Functions (Count, Sum, Avg, Min, Max)

Sql Aggregate Functions (Count, Sum, Avg, Min, Max)

Blog Bustle 21/03/2020

Sql aggregate functions perform various calculation on multiple rows in sql tables. Sql aggregate funtions perform calculation on single column of sql table. Sql aggregate functions return single value after calculation. Sql aggregate functions are used with sql select query.

Sql Aggregate functions

There are five sql aggregate functions given below:

1. COUNT - COUNT aggregate function is used to count number of records in sql query. It does not null values.

2. SUM - SUM aggregate function is used to calculate sum of given column values.

3. AVG - AVG aggregate function is used to calculate average of given column values.

4. MIN - MIN aggregate function is used to find minimum value of given column values.

5. MAX - MAX aggregate function is used to find maximum value of given column values.

Let's look at some examples to understand Sql aggregate functions.

I have student fees table.

id name class admission_fee session
1 justin 7 800.00 2020-2021
2 michael 7 800.00 2020-2021
3 kevin 6 700.00 2020-2021
4 robert 6 700.00 2020-2021

A. COUNT() example

Count total number of rows in student fees table.

Select COUNT(*) FROM student_fees;

After running this sql query, result will be

COUNT(*)
4

COUNT function with sql alias

Select COUNT(*) as total_students FROM student_fees;

After running this sql query, result will be

total_students
4

B. SUM() example

Calculate total admission fee of students in student fees table.

Select SUM(admission_fee) FROM student_fees;

After running this sql query, result will be

SUM(admission_fee)
3000.00

C. AVG() example

Calculate average admission fee of students in student fees table.

Select AVG(admission_fee) FROM student_fees;

After running this sql query, result will be

AVG(admission_fee)
750.000000

I want to display two digits after decimal point in average admission fee value. So we will use CAST() function in sql query.

SELECT CAST(AVG(admission_fee) AS DECIMAL(10,2)) as avg_fee FROM student_fees;

After running this sql query, result will be

avg_fee
750.00

D. MIN() example

Find minimum admission fee in student fees table.

SELECT MIN(admission_fee) FROM student_fees;

After running this sql query, result will be

MIN(admission_fee)
700.00

E. MAX() example

Find maximum admission fee in student fees table.

SELECT MAX(admission_fee) FROM student_fees;

After running this sql query, result will be

MAX(admission_fee)
800.00

Always remember that first select database before running any sql query related to that database otherwise we will get 'no database selected' error.

After looking at these examples, we can easily use sql aggregate functions. Hope you like this tutorial.

Other important links

Sql NOT IN operator

Sql IN operator

Sql NOT operator

Sql OR Operator

Sql AND Operator

Sql Aggregate Functions (Count, Sum, Avg, Min, Max) searches:

Tags: #mysql