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
SQL MAX() Example
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 |
