Aggregate Functions
6 methodsFunctions that operate on a set of rows and return a single value.
COUNT(expr)Return the number of non-null values of expr. COUNT(*) counts all rows.
Parameters
| Name | Type | Description |
|---|---|---|
| expr | any | Column or expression (or *). |
Returns
int
Example
sql
SELECT COUNT(*) FROM users; -- total rows
SELECT COUNT(email) FROM users; -- non-null emails
SELECT COUNT(DISTINCT country) FROM users; -- distinct countriesSUM(expr)Return the sum of expr across non-null values.
Parameters
| Name | Type | Description |
|---|---|---|
| expr | numeric | Numeric column or expression. |
Returns
numeric
Example
sql
SELECT SUM(amount) FROM orders;
SELECT product, SUM(quantity) AS total_qty
FROM sales GROUP BY product;AVG(expr)Return the arithmetic mean of expr across non-null values.
Parameters
| Name | Type | Description |
|---|---|---|
| expr | numeric | Numeric column or expression. |
Returns
numeric
Example
sql
SELECT AVG(price) FROM products;
SELECT category, AVG(price) AS avg_price
FROM products GROUP BY category;MIN(expr)Return the minimum value of expr across non-null values.
Parameters
| Name | Type | Description |
|---|---|---|
| expr | any | Column or expression. |
Returns
any
Example
sql
SELECT MIN(price) FROM products;
SELECT category, MIN(price) AS min_price
FROM products GROUP BY category;MAX(expr)Return the maximum value of expr across non-null values.
Parameters
| Name | Type | Description |
|---|---|---|
| expr | any | Column or expression. |
Returns
any
Example
sql
SELECT MAX(price) FROM products;
SELECT category, MAX(price) AS max_price
FROM products GROUP BY category;GROUP_CONCAT(expr SEPARATOR sep)Concatenate non-null values of expr into a single string. (MySQL; STRING_AGG in PostgreSQL / SQL Server.)
Parameters
| Name | Type | Description |
|---|---|---|
| expr | string | Column or expression to concatenate. |
| sep | string | Separator (default ','). |
Returns
string
Example
sql
-- MySQL
SELECT category, GROUP_CONCAT(name SEPARATOR ', ')
FROM products GROUP BY category;
-- PostgreSQL / SQL Server:
SELECT category, STRING_AGG(name, ', ')
FROM products GROUP BY category;