## Calculations

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

SQL command Result

SELECT COUNT(*)

FROM employee;

or

SELECT COUNT(surname)

FROM employee;

Count all the rows in the employee table

Any column name can be used but the result will be the same

SELECT SUM(salary)

FROM employee;

Sum the values in the salary column.

The column must be a numeric type

SELECT MIN(Salary), MAX(SALARY), AVG(salary)

FROM employee;

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)

FROM employee;

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.

Next: GROUP BY clause