Multi-Row Functions

Multi-row functions or aggregate functions are functions that accept multiple values and returns a single result. These can also accept any number of arguments, just like single-row functions.

These are the standard multi-row functions:

• SUM

• AVG

• COUNT

• MIN

• MAX

Determining the Sum

Assume you created a table that includes a column of numerical values. The name of the column is AGE and you are curious about the sum of all ages on the entire table. The following SQL determines the sum of all ages in available records:

SELECT SUM(AGE) FROM users;

This will only return one column and one row, forming a single cell that has holds the returned value from the function. The SQL statement is illustrated in the figure below:

Note: Use the AS keyword to rename the column in a view to make it meaningful and avoid confusion.

Determining the Average

The syntax of determining the average is almost the same as determining the sum, just different words to use. Suppose you have a table of employees that includes their monthly salary, then you are given a task to find out the average monthly salary of all employees. To accomplish the task, the AVG function is used to determine the average. Here is how:

SELECT AVG(AGE) AS average_salary FROM employees;

The output view has only one column, which is the average_salary, and also has only one row because multi-row functions only return a single cell or result. AS keyword is used to make the column meaningful.

Determining the Number of Selected Records

Counting selected records manually may be an easy task, but imagine using it as a basis for something or when counting an enormous number of records. The following example determines the number of employees in the database:

SELECT COUNT(*) number_of_employees FROM employees;

It does not matter which column is passed to the COUNT function's parameter. It can also be any of the columns in the table. If employees table has 56 records, it will then return 56 as a single cell in an output view.

Tip: When aliasing—giving an alias or new name to—a column, the AS keyword can be omitted for code shortening.

Determining the Minimum or Maximum Value

The purpose of MIN and MAX functions is, you guessed it, to determine the minimum or maximum value from a set of comparable values. Here are the following examples:

MIN:

SELECT MIN(age) youngest_employee FROM employees;

MAX:

SELECT MAX(salary) highest_salary FROM employees;

GROUP BY Clause

The purpose of this clause is to group results returned by a multi-row function by a necessarily non-unique column. Assume you have a table of users that contains 350 records, and you want to know how many users belong to each unique age.

SELECT 

  age,

  COUNT(*) count

FROM users

GROUP BY age

ORDER BY age;

The query above displays two columns: age and count. The age column displays the possible unique ages. The count column displays the number of users associated with the corresponding age, which is controlled by the GROUP BY clause.

The age column is only made possible to display if the GROUP BY clause selects that column, which is used to accumulate records that have the same age, then passes it to multi-row functions available (in this case, the COUNT function only) within the query and return a single result on each unique age.

Presumably, if 23 users are at the age of 16, then it returns 23 from the COUNT function, which of course corresponds to the age of 16.

HAVING Clause

This clause is similar to WHERE, but intended for the GROUP BY clause. HAVING clause basically filters out the results from the GROUP BY clause with the specified condition. 

Alternatively, you can still use the WHERE clause, then putting the GROUP BY clause next to it. HAVING clause is used after the GROUP BY clause. Just like putting WHERE clause after the SELECT statement.

The following example uses the same one above, but it includes the HAVING clause to display only ages below 20.

SELECT 

  age,

  COUNT(*) count

FROM users

GROUP BY age

HAVING age < 20;

ORDER BY age;