Computer Science · Ch 9 — Structured Query Language (SQL)
Aggregate Functions
Aggregate Functions
Aggregate functions are also called multiple row functions. Unlike single row functions that work on one row at a time, these functions operate on a set of records as a whole and return a single value for each column they are applied to.
The textbook draws a clear distinction between single row and multiple row functions. Single row functions process one row and produce one result per row. Multiple row functions (aggregate functions) process many rows and produce one result for the entire group of rows.
All aggregate functions require the column to be of numeric type — you cannot use them on text or date columns directly.
The Aggregate Functions Covered
AVG(column)
Returns the average (mean) of all values in the specified column.
Example: SELECT AVG(Price) FROM INVENTORY; gives the output 576091.625000 — that is the average price of all items in the inventory table.
SUM(column)
Returns the total sum of all values in the specified column.
Example: SELECT SUM(Price) FROM INVENTORY; gives 4608733.00 — the total value of all inventory items.
COUNT(*)
Returns the number of records (rows) in a table. This counts every row, regardless of whether any column contains NULL.
To count records that match a specific condition, you use COUNT(*) with a WHERE clause.
Example: SELECT COUNT(*) from MANAGER; returns 4 because the MANAGER table has four records.
COUNT(column)
Returns the number of non-NULL values in the specified column. NULL values are ignored.
The textbook gives a clear example: the MANAGER table has four records, but the MEMNAME column has one NULL value.
SELECT COUNT(MEMNAME) FROM MANAGER; returns 3, not 4, because the NULL entry is skipped.
Examples from the Textbook (Example 9.22)
a) Display the total number of records from table INVENTORY having a model as VXI.
SELECT COUNT(*) FROM INVENTORY WHERE Model="VXI";
Output: 2 — only two cars in the inventory have the model VXI.
b) Display the total number of different types of Models available from table INVENTORY.
SELECT COUNT(DISTINCT Model) FROM INVENTORY;
Output: 6 — there are six distinct model names in the table. The DISTINCT keyword ensures each model is counted only once, even if it appears multiple times.
c) Display the average price of all the cars with Model LXI from table INVENTORY.
SELECT AVG(Price) FROM INVENTORY WHERE Model="LXI";
Output: 548306.500000 — the average price of LXI model cars.
Important Points to Remember
COUNT(*)counts all rows, including those with NULL values in any column.COUNT(column)counts only non-NULL values in that specific column.DISTINCTcan be used withCOUNTto count only unique values. …
| Single Row Function | Multiple row function |
|---|---|
| 1. It operates on a single row at a time. | 1. It operates on groups of rows. |
| 2. It returns one result per row. | 2. It returns one result for a group of rows. |
| 3. It can be used in Select, Where, and Order by clause. | 3. It can be used in the select clause only. |
| Function | Description | Example with output |
|---|---|---|
| MAX(column) | Returns the largest value from the specified column. | mysql> SELECT MAX(Price) FROM INVENTORY; Output: 673112.00 |
| MIN(column) | Returns the smallest value from the specified column. | mysql> SELECT MIN(Price) FROM INVENTORY; Output: 355205.00 |
| AVG(column) | Returns the average of the values in the specified column. | mysql> SELECT AVG(Price) FROM INVENTORY; Output: 576091.625000 |
| SUM(column) | Returns the sum of the values for the specified column. | mysql> SELECT SUM(Price) FROM INVENTORY; Output: 4608733.00 |
| COUNT(*) | Returns the number of records in a table. Note: In order to display the number of records that matches a particular criteria in the table, we have to use COUNT(*) with WHERE clause. | mysql> SELECT COUNT() from MANAGER; Output: | count() | | 4 | |