AppDividend
Latest Code Tutorials

# SQL Aggregate Functions | Min, Max, Count, Avg, Sum In SQL

SQL aggregate functions are inbuilt functions that are used for performing various operations in data.  Aggregate Functions are used for performing operations on multiple rows of a particular column and result in a single value. An aggregate function allows you to perform the calculation on a set of values to return the single scalar value.

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

Aggregate Functions are:

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

Let’s understand all this function with examples.

## #Avg() in SQL

The avg() function is used to return an average value after the calculation 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() function is used to count a total number of records in a table with a condition 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 amount where more than 1000.

## #Max() in SQL

The max() function is used to return 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() function is used to return 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() function is 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() function is used to return 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() function is used to return 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.

Finally, SQL Aggregate Functions  (Min, Max, Count, Avg, Sum) Tutorial is over.

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