COUNT / SUM / AVG / MIN / MAXFunctions
Buy Me A Coffee

The five core aggregate functions. COUNT counts rows or non-NULL values. SUM and AVG compute totals and averages. MIN and MAX return the smallest and largest value. All are universally supported.

Functions that collapse multiple rows into a single value: COUNT, SUM, AVG, MIN, MAX, and more. Ignored by window function counterparts when used as window functions. Essential for summarization and analytics.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 3.23 All five functions supported. COUNT(*) counts all rows including NULLs; COUNT(col) counts non-NULL values only. AVG returns a floating-point result regardless of column type. COUNT(DISTINCT col) and SUM(DISTINCT col) are supported. In MySQL, AVG of an integer column returns a DECIMAL.
PostgreSQL ✓ Supported 6.0 All five functions supported, plus FILTER (WHERE ...) for conditional aggregation on any aggregate. AVG returns NUMERIC for integer/decimal inputs. count(*) is slightly faster than count(1). DISTINCT is supported inside all aggregates. ORDER BY inside aggregates is supported for ordered-set functions.
SQL Server ✓ Supported 6.5 All five functions supported. COUNT(*) vs COUNT(col) distinction applies. AVG performs integer division when the column is an integer type — cast to FLOAT or DECIMAL to avoid truncation. COUNT_BIG() variant returns BIGINT for very large tables. No FILTER clause — use SUM(CASE WHEN ... THEN 1 ELSE 0 END) as a workaround.
Oracle ✓ Supported 7 All five functions supported. COUNT(*) vs COUNT(col) distinction applies. AVG returns NUMBER. DISTINCT supported in all aggregates. No FILTER clause — use SUM(CASE WHEN ... END) pattern.
SQLite ✓ Supported 1.0 All five functions supported. COUNT(*) counts all rows; COUNT(col) skips NULLs. AVG always returns REAL. The FILTER (WHERE ...) clause is supported since SQLite 3.30.0.

Details

COUNT(*) and COUNT(col) are not interchangeable: COUNT(*) counts every row, COUNT(col) excludes rows where col is NULL. This distinction exists identically in all five engines. The most common portability trap is AVG integer division in SQL Server — always cast the input to FLOAT or DECIMAL when averaging integer columns. The FILTER (WHERE ...) clause for conditional aggregation is supported in PostgreSQL, SQLite 3.30+, and is part of the SQL standard — but not in MySQL or SQL Server, where CASE WHEN inside SUM/COUNT is the portable workaround.

Standard Syntax

SELECT COUNT(*) AS total_rows, COUNT(email) AS rows_with_email, SUM(amount) AS total_amount, AVG(amount) AS avg_amount, MIN(created_at) AS earliest, MAX(created_at) AS latest FROM orders;

Version Support

MySQL: Since 3.23 PostgreSQL: Since 6.0 SQL Server: Since 6.5 Oracle: Since 7 SQLite: Since 1.0

Per-Database Syntax & Notes

MySQL

All five functions supported. COUNT(*) counts all rows including NULLs; COUNT(col) counts non-NULL values only. AVG returns a floating-point result regardless of column type. COUNT(DISTINCT col) and SUM(DISTINCT col) are supported. In MySQL, AVG of an integer column returns a DECIMAL.

-- Core usage: SELECT COUNT(*) AS total_rows, COUNT(email) AS non_null_emails, COUNT(DISTINCT email) AS unique_emails, SUM(amount) AS total, AVG(amount) AS average, MIN(created_at) AS first_order, MAX(created_at) AS last_order FROM orders; -- With GROUP BY: SELECT dept, COUNT(*) AS headcount, AVG(salary) AS avg_salary, MAX(salary) AS top_salary FROM employees GROUP BY dept;

PostgreSQL

All five functions supported, plus FILTER (WHERE ...) for conditional aggregation on any aggregate. AVG returns NUMERIC for integer/decimal inputs. count(*) is slightly faster than count(1). DISTINCT is supported inside all aggregates. ORDER BY inside aggregates is supported for ordered-set functions.

-- Core usage: SELECT COUNT(*) AS total_rows, COUNT(email) AS non_null_emails, SUM(amount) AS total, AVG(amount) AS average, MIN(created_at) AS first_order, MAX(created_at) AS last_order FROM orders; -- FILTER for conditional aggregation (PostgreSQL-standard, also supported in SQLite 3.30+): SELECT COUNT(*) FILTER (WHERE status = 'completed') AS completed_count, SUM(amount) FILTER (WHERE region = 'US') AS us_total FROM orders;

SQL Server

All five functions supported. COUNT(*) vs COUNT(col) distinction applies. AVG performs integer division when the column is an integer type — cast to FLOAT or DECIMAL to avoid truncation. COUNT_BIG() variant returns BIGINT for very large tables. No FILTER clause — use SUM(CASE WHEN ... THEN 1 ELSE 0 END) as a workaround.

-- Core usage: SELECT COUNT(*) AS total_rows, COUNT(email) AS non_null_emails, SUM(amount) AS total, AVG(amount) AS avg_amount, MIN(created_at) AS first_order, MAX(created_at) AS last_order FROM orders; -- Avoid integer AVG truncation: SELECT AVG(CAST(salary AS FLOAT)) AS avg_salary FROM employees; -- Conditional aggregation (FILTER workaround): SELECT SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count, SUM(CASE WHEN region = 'US' THEN amount ELSE 0 END) AS us_total FROM orders;

Oracle

All five functions supported. COUNT(*) vs COUNT(col) distinction applies. AVG returns NUMBER. DISTINCT supported in all aggregates. No FILTER clause — use SUM(CASE WHEN ... END) pattern.

SELECT COUNT(*) AS total_rows, COUNT(email) AS non_null_emails, SUM(amount) AS total, AVG(amount) AS average, MIN(created_at) AS first_order, MAX(created_at) AS last_order FROM orders; -- Conditional aggregation: SELECT SUM(CASE WHEN status = 'COMPLETE' THEN 1 ELSE 0 END) AS completed, SUM(CASE WHEN region = 'US' THEN amount ELSE 0 END) AS us_total FROM orders;

SQLite

All five functions supported. COUNT(*) counts all rows; COUNT(col) skips NULLs. AVG always returns REAL. The FILTER (WHERE ...) clause is supported since SQLite 3.30.0.

SELECT COUNT(*) AS total_rows, COUNT(email) AS non_null_emails, SUM(amount) AS total, AVG(amount) AS average, MIN(created_at) AS first_order, MAX(created_at) AS last_order FROM orders; -- FILTER clause (3.30.0+): SELECT COUNT(*) FILTER (WHERE status = 'completed') AS completed_count FROM orders;