COUNT/SUM/AVG/MIN/MAX
Aggregate functions collapse many rows into a single summary value.
In this page:
Syntax
SELECT COUNT(*), SUM(column), AVG(column), MIN(column), MAX(column)
FROM table_name;COUNT/SUM/AVG/MIN/MAX
COUNT counts rows, SUM adds, AVG averages, MIN and MAX find extremes. Except COUNT(*), they ignore NULLs. Without GROUP BY an aggregate query returns one row. AVG of integers returns a decimal in PostgreSQL. COUNT(DISTINCT col) counts unique values.
Note:
COUNT(*) counts rows, COUNT(col) counts non-null values.
Example: COUNT/SUM/AVG/MIN/MAX
CREATE TABLE sales (id INTEGER PRIMARY KEY, item TEXT, qty INTEGER, price INTEGER);
INSERT INTO sales VALUES (1, 'pen', 5, 2), (2, 'book', 1, 12), (3, 'pen', 10, 2), (4, 'lamp', 2, 30);
SELECT COUNT(*) AS n, SUM(qty) AS total_qty, AVG(price) AS avg_price, MIN(price) AS cheapest, MAX(price) AS priciest FROM sales;
-- Output:
-- n | total_qty | avg_price | cheapest | priciest
-- 4 | 18 | 11.5 | 2 | 30Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Mixing aggregated and plain columns without GROUP BY
- Forgetting NULLs are ignored
- Expecting AVG of integers to be an integer
Chapter Summary
- COUNT SUM AVG MIN MAX
- Aggregates ignore NULL
- COUNT(*) counts every row
- One row without GROUP BY
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: