# SQL Funcations

SQL Functions

SQL has many built-in functions for performing calculations on data.

## SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

AVG() - Returns the average value

COUNT() - Returns the number of rows

MAX() - Returns the largest value

MIN() - Returns the smallest value

SUM() - Returns the sum

## The SUM() Function

The SUM() function returns the total sum of a numeric column.

### SQL SUM() Syntax

SELECT SUM(column_name) FROM table_name

## SQL SUM() Example

We have the following "Order" table: Now we want to find the sum of all "OrderPrice" fields".

We use the following SQL statement:

SELECT SUM(OrderPrice) AS OrderTotal FROM Order

The result-set will look like this:

 OrderTotal 5700

## The AVG() Function

The AVG() function returns the average value of a numeric column.

### SQL AVG() Syntax

SELECT AVG(column_name) FROM table_name

## SQL AVG() Example

We have the following "Order" table: Now we want to find the average value of the "OrderPrice" fields. We use the following SQL statement:

SELECT AVG(OrderPrice) AS OrderAverage FROM Order

The result-set will look like this:

 OrderAverage 950

Now we want to find the customers that have an OrderPrice value higher than the average OrderPrice value. We use the following SQL statement:

SELECT Customer FROM Order
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Order)

The result-set will look like this:

 Customer Ayan Anya Aneesh

# SQL COUNT() Function

The COUNT() function returns the number of rows that matches a specified criteria.

### SQL COUNT(column_name) Syntax

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name

### SQL COUNT(*) Syntax

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name

### SQL COUNT(DISTINCT column_name) Syntax

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name

Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

## SQL COUNT(column_name) Example

We have the following "Order" table: Now we want to count the number of orders from "Customer Ayan".We use the following SQL statement:

SELECT COUNT(Customer) AS CustomerAyan FROM Order
WHERE Customer='Ayan'

The result of the SQL statement above will be 1, because the customer Ayan has made 1 order in total:

 CustomerAyan 1

## SQL COUNT(*) Example

If we omit the WHERE clause, like this:

SELECT COUNT(*) AS NumberOfOrders FROM Order

The result-set will look like this:

 NumberOfOrders 6

which is the total number of rows in the table.

## SQL COUNT(DISTINCT column_name) Example

Now we want to count the number of unique customers in the "Orders" table. We use the following SQL statement:

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Order

The result-set will look like this:

 NumberOfCustomers 6

## The MAX() Function

The MAX() function returns the largest value of the selected column.

### SQL MAX() Syntax

SELECT MAX(column_name) FROM table_name

## We have the following "Order" table: Now we want to find the largest value of the "OrderPrice" column. We use the following SQL statement:

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Order

result will look like this:

 LargestOrderPrice 2000

## The MIN() Function

The MIN() function returns the smallest value of the selected column.

### SQL MIN() Syntax

SELECT MIN(column_name) FROM table_name

## SQL MIN() Example

Now we want to find the smallest value of the "OrderPrice" column. We use the following SQL statement:

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Order

result will look like this:

 SmallestOrderPrice 100 