AGGREGATE FUNCTIONS
In certain cases, you are not actually interested in the data themselves but more in information about these data. For instance, you may want to know the number of rows in a certain table. A number of functions are available in SQL that help you to derive such information about the tables in your database. The following aggregate functions can be used:
| COUNT ( [column] ) | counts the number of rows in a table |
| SUM ( [column] ) | adds all the numerical values in the specified column |
| AVG ( [column] ) | calculates the average of all the values in the specified
column |
| MIN ( [column] ) | returns the minimum of all the numeric values in
the specified column |
| MAX ( [column] ) | returns the maximum of all the numeric values in the specified column |
When you use one of these functions, the result of the query will contain one
row and one column only. This single cell contains the number that is the result
of the statistical function that has been applied. An example is given below.
SELECT COUNT(*)
FROM CITY;
This query will return one row and one cell which contains the number of cities that are present in the table CITY.
COUNT() takes the name of a column as a paramter. This parameter should be included within parentheses. The function will count all the records for which the specified column is not empty. The best way to ensure that all the columns are selected is by using the asterisk (*) as a parameter, as in the example above. Each record in which any of the fields is not empty will be included in the count.
If you want to ensure that double values are not counted twice, you can of course use DISTINCT in order to get rid of these double values before you start counting. The following query counts the number of unique subject codes that have been used in the table TREASURE.
SELECT COUNT(DISTINCT SUBJECT)
FROM TREASURE ;
The query below selects the oldest year of birth in the table CREATOR:
SELECT MIN(YEAR_OF_BIRTH)
FROM CREATOR ;
Exercises
- Select the year of the oldest treasure in the database
- Count the number of different countries in the table TREASURE
- Find the most recent year of death in the table PERSON