# SQL Aggregate Functions: Min, Max, Count, Avg, Sum

We often use the aggregate functions with the SELECT statement’s GROUP BY and HAVING clauses.

## SQL aggregate functions

SQL aggregates are built-in functions used for various operations on multiple rows of a particular column, resulting in a single value.

An aggregate function allows you to perform the calculation on a set of values to return a single scalar value.

Aggregate Functions are:

1. AVG()
2. COUNT()
3. MAX()
4. MIN()
5. SUM()
6. STDDEV()
7. VARIANCE()

Let’s understand all these functions with examples.

## Avg in SQL

The avg() is a built-in SQL function that returns an average value after the calculation is performed in a numeric column.

#### Syntax

```Select avg (column_name) from table_name;
```

Consider the following table.

Consider Table: (Customers)

 CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

#### Query

```select avg(amount) from customers;
```

#### Explanation

The above query resulted in the average salary of customers in a table customer.

## Count in SQL

The count() is a built-in SQL aggregate function used to count the total number of records in a table with or without a condition.

#### Syntax

`Select count(column_name) from table_name;`

Consider Table: (CUSTOMERS)

 CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

#### Query: (Without a condition)

```Select count(cust_code) from customers;
```

See the following output.

#### Explanation

The above query resulted in the total number of customers in a table.

#### Query: (With a condition)

```Select count(cust_code) from customers where amount > 10000;
```

#### Explanation

The above query returned the total number of customers whose amounts were more than 1000.

## Max in SQL

The max() is a built-in SQL function that returns the maximum value in a column.

#### Syntax

```Select max(column_name) from table_name;
```

Consider Table: (CUSTOMERS)

 CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

#### Query

```Select max(amount) from customers;
```

#### Explanation

The max() function resulted in the max amount present in the customer table.

## Min in SQL

The min() is a built-in SQL function that returns the minimum value in a column.

#### Syntax

```Select MIN(column_name) from table_name;
```

Consider Table: (CUSTOMERS)

 CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

#### Query

`Select min(amount) from customers;`

#### Explanation

The min() function query resulted in the min amount present in the customer table.

## Sum in SQL

The sum() is a built-in SQL function used to return the submission of all numeric values in a column.

#### Syntax

```Select SUM(column_name) from table_name;
```

Consider Table: (CUSTOMERS)

 CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

#### Query

```Select sum(amount) from customers;
```

#### Explanation

The sum() function query resulted in the total sum of the amount column.

## STDDEV in SQL

The stddev() is a built-in SQL function that returns the standard deviation of a selected column.

#### Syntax

```Select STDDEV(column_name) from table_name;
```

Consider Table: (CUSTOMERS)

 CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

#### Query

```Select STDDEV(amount) from customers;
```

#### Explanation

The STDDEV query resulted in the standard deviation of an amount column.

## Variance in SQL

The variance() is a built-in SQL function that returns the Variance of a selected column.

#### Syntax

```Select VARIANCE(column_name) from table_name;
```

Consider Table: (CUSTOMERS)

 CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000

#### Query

```Select variance(amount) from customers;
```

#### Explanation

The above query resulted in the variance of the amount column.

That’s it for this tutorial.

Categories SQL

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