Informatics Practices · Ch 1 — Querying and SQL Functions
Aggregate Functions
Aggregate Functions
Aggregate functions are also called multiple-row functions. Unlike single-row functions that work on one row at a time, aggregate functions work on a whole set of records at once. For each column you apply them to, they return a single value — the sum, count, average, and so on — for that entire group of rows.
The textbook draws a clear distinction between single-row and multiple-row functions. Single-row functions process one row and give one output per row. Multiple-row (aggregate) functions process many rows and give one output for the whole set. The column you use an aggregate function on must be of numeric type — you cannot, for example, take the SUM of a text column.
Here are the main aggregate functions covered:
-
SUM(column) — Returns the total (sum) of all values in the specified column. For example,
SELECT SUM(Price) FROM INVENTORY;gives the output4608733.00. -
COUNT(column) — Returns the number of non-NULL values in the specified column. It ignores NULL entries. The textbook uses a MANAGER table with four records: MNO values 1, 2, 3, 4 and MEMNAME values AMIT, KAVREET, KAVITA, and NULL.
SELECT COUNT(MEMNAME) FROM MANAGER;returns 3, because the NULL value is not counted. -
COUNT(*) — Returns the total number of records (rows) in the table, regardless of NULLs.
SELECT COUNT(*) from MANAGER;returns 4. You can also use COUNT(*) with a WHERE clause to count only those rows that match a condition. For instance,SELECT COUNT(*) FROM INVENTORY WHERE Model="VXI";returns 2, meaning there are two records in the INVENTORY table where the model is VXI. -
COUNT(DISTINCT column) — Returns the number of distinct (unique) values in the column, ignoring duplicates. Example:
SELECT COUNT(DISTINCT Model) FROM INVENTORY;returns 6, meaning there are six different model types in the table. -
AVG(column) — Returns the average (mean) of the values in the specified column. Example:
SELECT AVG(Price) FROM INVENTORY WHERE Model="LXI";returns548306.500000, the average price of all cars with model LXI. …
| Single_row Functions | Multiple_row functions |
|---|---|
| 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(column) | Returns the number of values in the specified column ignoring the NULL values. Note: In this example, let us consider a MANAGER table having two attributes and four records. | mysql> SELECT * from MANAGER; Output: +------+---------+ | MNO | MEMNAME | +------+---------+ | 1 | AMIT | | 2 | KAVREET | | 3 | KAVITA | | 4 | NULL | +------+---------+ 4 rows in set (0.00 sec) mysql> SELECT COUNT(MEMNAME) FROM MANAGER; Output: +----------------+ | COUNT(MEMNAME) | +----------------+ | 3 | +----------------+ 1 row in set (0.01 sec) |