SQL: Top Customers and Key Accounts with GROUP BY and HAVING

Why this matters

Before the quarterly review the owner of Lakeside Office Supply (made up) wants two lists: the top three customers, and every key account worth $3,000 or more this quarter, so each one gets a thank-you call.

In a spreadsheet that means sorting, subtotalling and checking by hand. In SQL it is four short queries on the same orders table: total each customer, sort, keep the top three, and filter the totals with HAVING.

What you’ll build

A top-three list (Cobalt Legal $4,440, Northside Realty $4,030, Maple Dental $2,230) and a key accounts list with the number of orders next to each.

Key accounts at $3,000 or more: Cobalt Legal (3 orders, $4,440) and Northside Realty (2 orders, $4,030).

What you need

Nothing to start the practiceThe practice is built from real DB Fiddle screens and runs in this page.
A browser, to do it for realDB Fiddle is free and needs no account.

Concepts

3 ideas you’ll use in the practice.

One row per customer

SELECT customer, SUM(amount) AS revenue FROM orders GROUP BY customer gives one row per customer with their total. Every column in the SELECT line must either be in the GROUP BY or be added up (SUM, COUNT, MAX).

ORDER BY ... DESC and LIMIT

ORDER BY revenue DESC sorts the biggest first; ASC (the default) puts the smallest first. LIMIT 3 keeps only the first three rows, which turns any sorted list into a top-N list.

HAVING filters totals

WHERE filters single orders before they are added up. HAVING filters the totals after GROUP BY. "Customers worth $3,000 or more" is about totals, so it is HAVING SUM(amount) >= 3000.

The lines always go in this order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

Practice

Do every step yourself in a practice copy of the real tool. It checks your work, and “Do it for me” finishes any step you get stuck on.

DB FiddleSQL: Top Customers and Key Accounts with GROUP BY and HAVING14 steps · ~12 min · nothing touches your real accounts

Start the practice →Prefer to read? Step-by-step guide

Do it for real

Now repeat it in your own account. Tick each step as you go.

Open db-fiddle.com (MySQL, no account) and type the orders table in Schema SQL, as in module 1.
In Query SQL type SELECT customer, SUM(amount) AS revenue, then FROM orders, then GROUP BY customer; and click Run.
Delete the last semicolon, press Enter, type ORDER BY revenue DESC; and click Run.
Delete the semicolon again, press Enter, type LIMIT 3; and click Run: that is your top three.
Replace the query: type SELECT customer, COUNT(*) AS orders, then SUM(amount) AS revenue, then FROM orders, then GROUP BY customer, then HAVING SUM(amount) >= 3000, then ORDER BY revenue DESC; and click Run.
Change 3000 to your own key-account threshold and run it again.

Quick check

3 questions to check you’ve got it.

1. Which line keeps only customers whose total is $3,000 or more?

WHERE amount >= 3000
HAVING SUM(amount) >= 3000
LIMIT 3000

Show the answer

HAVING SUM(amount) >= 3000. The threshold is on the total per customer, so it goes in HAVING after GROUP BY. WHERE would look at single orders.

2. What does ORDER BY revenue DESC do?

Sorts biggest first
Sorts smallest first
Removes the revenue column

Show the answer

Sorts biggest first. DESC means descending: the largest revenue comes first.

3. With the sample data, how many key accounts are there at $3,000 or more?

One
Two
Four

Show the answer

Two. Cobalt Legal ($4,440) and Northside Realty ($4,030). Maple Dental is $2,230.

Up next · Module 3

SQL: Find Customers Who Have Gone Quiet with a LEFT JOIN

Next: the customers who are not ordering any more, with a second table and a LEFT JOIN.

Start module 3 →