SQL Aggregate Functions

Aggregate functions turn many rows into one number: a count, a total, an average, a minimum or a maximum. They're how you go from raw orders to metrics like revenue, average order value and best-selling price point.

The five core aggregates

Syntax

SELECT AGGREGATE_FUNCTION(column)
FROM table_name
WHERE condition;

Examples

1. COUNT rows

How many products does ShopKart sell?

SELECT COUNT(*) AS product_count
FROM products;

Result:

(1 row)

2. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)

SELECT COUNT(*) AS all_orders,
       COUNT(delivery_date) AS orders_with_delivery,
       COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Result:

(1 row)

Three different numbers from the same table. Pick the one that matches the question.

3. SUM: total revenue

Net revenue across all order lines, after discounts:

SELECT ROUND(SUM(quantity * unit_price * (1 - discount_pct / 100)), 2) AS net_revenue,
       SUM(quantity) AS units_sold
FROM order_items;

Result:

(1 row)

4. AVG, MIN and MAX

SELECT ROUND(AVG(price), 2) AS avg_price,
       MIN(price) AS cheapest,
       MAX(price) AS most_expensive
FROM products;

Result:

(1 row)

MIN and MAX also work on dates and text:

SELECT MIN(order_date) AS first_order,
       MAX(order_date) AS latest_order
FROM orders;

Result:

(1 row)

5. Aggregates with WHERE

WHERE filters rows before they're aggregated. Average delivery time for delivered orders:

SELECT COUNT(*) AS delivered_orders,
       ROUND(AVG(delivery_date - order_date), 1) AS avg_days_to_deliver
FROM orders
WHERE status = 'Delivered';

Result:

(1 row)

6. Several conditions in one pass with FILTER

PostgreSQL's FILTER clause applies a condition to a single aggregate:

SELECT COUNT(*) AS total,
       COUNT(*) FILTER (WHERE payment_method = 'UPI') AS upi_orders,
       COUNT(*) FILTER (WHERE payment_method = 'COD') AS cod_orders
FROM orders;

Result:

(1 row)

In other databases, write SUM(CASE WHEN payment_method = 'UPI' THEN 1 ELSE 0 END) instead (see CASE WHEN).

Common mistakes

Mixing aggregated and non-aggregated columns. The database can't show one product_name next to a single MAX(price):

SELECT product_name, MAX(price)
FROM products;

Error: column "products.product_name" must appear in the GROUP BY clause or be used in an aggregate function

To find which product is most expensive, sort and limit, or use GROUP BY and subqueries.

• Averaging an average. The average of monthly averages is not the overall average if months have different order counts.
• Forgetting NULL handling. AVG(col) skips NULLs instead of treating them as 0.

Practice question

How many order lines had a discount, and what was the average discount on those lines?

SELECT COUNT(*) AS discounted_lines,
       ROUND(AVG(discount_pct), 2) AS avg_discount_pct
FROM order_items
WHERE discount_pct > 0;

Key takeaways

• Aggregates collapse many rows into one value.
• COUNT(*) counts rows; COUNT(col) skips NULLs; COUNT(DISTINCT col) counts unique values.
• Aggregates ignore NULLs, and return NULL when no rows match.
• You can't mix aggregated and plain columns without GROUP BY.