PostgreSQL Count Sum Avg
COUNT(), SUM(), and AVG() are the workhorse aggregates for tallying rows, totaling amounts, and computing averages.
COUNT: counting rows
COUNT() tells you how many rows matched your query. COUNT(*) counts every row, while COUNT(column) counts only the rows where that column is not NULL. You can also combine COUNT with DISTINCT to count unique values.
Count rows and distinct values
SELECT
COUNT(*) AS total_orders,
COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;SUM: totaling numeric columns
SUM() adds together every non-null value in a numeric column. It is the natural choice for totals like revenue, quantity sold, or hours worked.
Total revenue by order status
SELECT status, SUM(total_amount) AS revenue
FROM orders
GROUP BY status;AVG: computing the mean
AVG() divides the sum of a column by the number of non-null rows, giving you the arithmetic mean. PostgreSQL returns a numeric type with full precision, so you may want to round the result for display.
Average order value, rounded
SELECT ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
WHERE order_date >= '2024-01-01';- COUNT(*) counts all rows, including those with NULLs
- COUNT(column) skips NULL values in that column
- SUM(column) returns NULL if every value in the group is NULL
- AVG(column) is equivalent to SUM(column) / COUNT(column)
- All three can be combined in a single SELECT list
