User Menu

Login



Share with

SQL Function PDF Print E-mail

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:

sqlexampletable

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:

sqlexampletable

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:

sqlexampletable

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

SQL MAX() Example

We have the following "Order" table:

sqlexampletable

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

scroll back to top