Apache Drill supports the Aggregation functions that we can use to apply on the input expressions such as AVG function will return the average value, COUNT function will return the number of rows matches in the expression, MIN and Max function will return the minimum and maximum value of the expression, SUM function will sum the expression and so on.

The following is the list of Drill Aggregate functions.

Drill Aggregate Functions |
Argument Type |
---|---|

ANY_VALUE(expression) | BIT, INT, BIGINT, FLOAT4, FLOAT8, DATE, TIMESTAMP, TIME, VARCHAR, VARBINARY, LIST, MAP, INTERVAL, INTERVALDAY, INTERVALYEAR, VARDECIMAL |

AVG(expression) | SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL, INTERVAL |

BOOL_AND(expression), BOOL_OR(expression) | BIT |

BIT_AND(expression), BIT_OR(expression) | INT, BIGINT |

COUNT(*) | * |

COUNT([DISTINCT] expression) | any |

MAX(expression), MIN(expression) | BINARY, DECIMAL, VARCHAR, DATE, TIME, or TIMESTAMP |

SUM(expression) | SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL, INTERVAL |

Now we will see a couple of examples of the Drill Aggregate function.

__1. Apache Drill AVG Function__

__1. Apache Drill AVG Function__

Drill AVG function works on the expression and returns the average value of the numerical expression.

**AVG Function Syntax:**

The following is the syntax of the AVG Function.

```
```
AVG([ALL | DISTINCT] expression)

**AVG Example**

In the below example, we are taking the average salary of employee_id 1, 2, 3,4,5;

__Command:__

__Command:__

*AVG*(salary) FROM cp.`employee.json` WHERE employee_id IN (1, 2, 3,4,5);

__Output:__

__Output:__

__2. Apache Drill COUNT(*) Function__

__2. Apache Drill COUNT(*) Function__

The drill count function is used to count the number of records, for example, if we are passing "*" then it will count the number of records present in the dataset.

**COUNT(*) Function Syntax:**

The following is the syntax of the COUNT(*) Function.

```
```
COUNT(*)

**COUNT(*) Example**

We will count the number of records present in the "employee.json" file.

__Command:__

__Command:__

*COUNT*(*) from cp.`employee.json`;

__Output:__

__Output:__

__3. Apache Drill MIN and MAX
Function__

__3. Apache Drill MIN and MAX Function__

Drill MIN and MAX functions are used to return the minimum and maximum values of an expression.

**MIN and MAX Function Syntax:**

The following is the syntax of the MIN and MAX Function.

```
```
MIN(expression)
MAX(expression)

**MIN and MAX Function Examples**

In this example, we will see the minimum and maximum birth date of the employee.

__Command:__

__Command:__

*MIN*(birth_date) FROM cp.`employee.json`;

*MAX*(birth_date) FROM cp.`employee.json`;

__Output:__

__Output:__