# SQL Aggregate Functions

Filed Under: SQL

SQL Aggregate Functions basically operate on multiple columns to perform the operations and serve to represent the output as an entity representing the operation executed.

Syntax:

``aggregate_function (DISTINCT | ALL expression)``
• DISTINCT enables the user to select distinct values from the table i.e. if multiple attributes contain the same value, then only single distinct value is considered for the calculations.
• ALL makes sure that even the repeated values are considered for the calculations.

## Aggregate Functions in SQL:

• AVG()
• MIN()
• MAX()
• SUM()
• COUNT()

### 1. SQL AVG() function

SQL AVG() function returns the average of all the selected values from the corresponding column of the table.

Let’s us consider the following table to understand the Aggregate functions:

Table Name: Details

Example:

```select AVG(Amount) from Details;
```

Output:

``9875``

### 2. SQL MIN() function

SQL MIN() function returns the minimum value of all the selected values from the corresponding column of the table.

Example:

```select MIN(Amount) from Details;
```

Output:

``2500``

### 3. SQL MAX() function

SQL MAX() function returns the maximum value of all the selected values from the corresponding column of the table.

```select MAX(Amount) from Details;
```

Output:

``20000``

### 4. SQL SUM() function

SQL SUM() function returns the summation of all the selected values from the corresponding column of the table.

#### Example 1: Basic Understanding of SUM() Function

```select SUM(Amount) from Details;
```

Output:

``39500``

#### Example 2: SQL SUM() with GROUP BY Clause

SQL SUM() can be framed together with SQL GROUP BY Clause to represent the output results by a particular label/values.

```SELECT SUM(Amount),Name
FROM Details
WHERE Age>21
GROUP BY Name;
```

Output:

#### Example 3: SQL SUM() with HAVING Clause

SQL SUM() function can be used along with SQL HAVING Clause; HAVING Clause is basically used to specify the condition to be operated on the set of values in the table.

```SELECT SUM(Amount),Name
FROM Details
GROUP BY Name
HAVING SUM(Amount)>2500;
```

Output:

### 5. SQL COUNT() function

#### Example1: COUNT() function to return the count of a particular column of a table

```select COUNT(Amount) from Details;
```

Output:

``4``

#### Example 2: COUNT(*) function

This function returns the count of all the values present in the set of records of a table.

```SELECT count(*) from Details;
```

Output:

``4``

#### Example 3: COUNT() with WHERE Clause

```SELECT count(*) from Details
where Age<25;
```

Output:

``2``

#### Example 4: COUNT() with GROUP BY Clause

```SELECT count(Amount),Name from Details
where Age<25
Group by Name;
```

Output:

#### Example 5: COUNT() with HAVING Clause

```SELECT count(Amount),Age, Name from Details
Group by Name
HAVING Age>=25;
```

Output:

## Reference

1. PAVAN KUMAR T S says:

the topics listed in ur website/blog helped me a lot in understanding SQL language much deeply …
even though few topics are little confusing by nature, Journal Dev has put it across very well for everybody’s understanding
thanks So much
Regards
Pavan T S