SQL Aggregate Functions

Easy5 min Save for later

Question

Interview insights are lockedSee how often this is asked and which companies asked it with Buncho Pro.

Problem

You have an orders table.

Table: orders

id	customer_id	total_amount
1	101	25.00
2	102	35.50
3	101	42.25
4	103	20.75
5	102	60.00

Task

Return the number of orders, the total sales, the average, the highest and the lowest order value.
Return the number of orders and the total spent per customer.

Expected output (part 1)

num_orders	total_sales	avg_order_value	max_order_value	min_order_value
5	183.50	36.70	60.00	20.75

Work out your queries on your own, then open Answers & Optimization to compare.

Hints

Answers

2 ways to solve it

A basic answer first, then the optimized one.

Basic
Optimized

Quick answer

COUNT(*) counts rows. COUNT(col) skips NULLs.
SUM, AVG, MIN and MAX ignore NULLs. On an empty set they return NULL.
Without GROUP BY the aggregate covers the whole result set. With it, one result per group.

1Basic

One query per metric

1SELECT COUNT(id)          AS num_orders      FROM orders;2SELECT SUM(total_amount)  AS total_sales     FROM orders;3SELECT AVG(total_amount)  AS avg_order_value FROM orders;4SELECT MAX(total_amount)  AS max_order_value FROM orders;5SELECT MIN(total_amount)  AS min_order_value FROM orders;6 7SELECT customer_id, COUNT(id) AS num_orders, SUM(total_amount) AS total_spent8FROM orders9GROUP BY customer_id;

Cost: five full scans of the table for part 1

2Optimized

All metrics in one scan

1SELECT COUNT(*)                     AS num_orders,2       SUM(total_amount)            AS total_sales,3       ROUND(AVG(total_amount), 2)  AS avg_order_value,4       MAX(total_amount)            AS max_order_value,5       MIN(total_amount)            AS min_order_value6FROM orders;7 8SELECT customer_id,9       COUNT(*)           AS num_orders,10       SUM(total_amount)  AS total_spent,11       SUM(CASE WHEN total_amount > 40 THEN 1 ELSE 0 END) AS big_orders  -- conditional aggregate12FROM orders13GROUP BY customer_id14ORDER BY total_spent DESC;

Why: one pass over the data instead of five
CASE inside an aggregate counts a condition without a second query
PostgreSQL also has COUNT(*) FILTER (WHERE ...)

Spotted a mistake in these answers?

Report a problem