SQL can perform extensive calculations on the data in a table before returning the results. We shall be looking at some of these functions to perform calculations.
The functions we will be looking at are:
COUNT counts the number of rows in a table based on some criteria
SUM sum, ie total, of the values for a named column
MIN minimum value for a named column
MAX maximum value for a named column
AVG average value for a named column
GROUP BY defines a column(s) to group together before performing one of the functions above
HAVING limits the rows returned by the GROUP BY clause
In addition, there are functions to change the format of a column, two of which are:
UPPER converts a character string to upper case characters
LOWER converts a character string to lower case characters
There are many other functions that you might like to investigate for yourself.
The use of these functions is best explained by looking at examples on their use.
COUNT, SUM, MIN, MAX, AVG clause
Count all the rows in the employee table
Any column name can be used but the result will be the same
Sum the values in the salary column.
The column must be a numeric type
SELECT MIN(Salary), MAX(SALARY), AVG(salary)
|Calculate the minimum, maximum and average values of the salary|
In the above examples, the only column that can be specified in the SELECT command is the function(s) itself.
For example, it is illegal to have the following SQL SELECT command:
SELECT forename, surname, MIN(Salary)
This will not return the name of the employee who has the lowest salary. The level of complexity of the necessary SQL SELECT command is outside the scope of this unit. However, if you wish to find out more refer to the references at the end of this guide.