SQL Subqueries Explained Simply: Customers Above Average Spend
A subquery is a query inside another query. It works out a number you don't know yet, like the average, and the outer query uses it. Find orders above average, customers who ordered in December, and customers who spend more than average.
Step by step
Query in a query
- A query inside a query. Which customers spend more than average? To answer that, you first need the average, a number you don't know yet. A subquery works it out for you. Let's build one.
The inner query
- The inner query. Start with the question inside the question. What's the average order total? Average, in brackets, order total. One row, one number. The average order is about a hundred and nine.
SELECT AVG(order_total) AS avg_order FROM orders;
Use the answer
- Inside WHERE. Now put that query in brackets, inside a where line. Order total greater than the average. The database runs the inner query first, then uses its answer. Fifty three orders beat the average. And if new orders come in, the average updates by itself. No number typed by hand.
SELECT order_id, order_total FROM orders WHERE order_total > (SELECT AVG(order_total) FROM orders);
A list
- Inside IN. A subquery can also give back a list. Here, the customer I Ds of every December order. In then keeps the customers on that list. Nine customers ordered in December. A ready list for a thank you email.
SELECT first_name, last_name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= '2025-12-01');
Above average spend
- Spend per customer. Back to our question. First, total spend per customer. Group the orders by customer ID and add up the totals. Thirty four customer I Ds, each with what they spent in total.
SELECT customer_id, SUM(order_total) AS spend FROM orders GROUP BY customer_id; - Above average. Now keep the big spenders with having. The subquery works out average spend per customer: all sales divided by the number of different customers. That's about four hundred and fifty. Fifteen customers spend more than the average customer. That's the answer to our question, in one query.
customer_id HAVING SUM(order_total) > (SELECT SUM(order_total) / COUNT(DISTINCT customer_id) FROM orders);
Recap
- Subqueries. So, a subquery is a query in brackets inside another query. It runs first. Use it for a number you don't know yet, like an average, or for a list to match with in.
