BBampaa

Lessons › SQL › Go further in SQL: Business questions

SQL lesson

Which customers came back?

Which customers came back? Coming back means more than one order. That's a rule about a total (each customer's count of orders), so it goes in HAVING, after GROUP BY. WHERE only sees single rows, before they're grouped.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

A rule about a total goes in HAVING

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1

Result:

customer_idorders
C1013
C1022
C1032
C1042
C1052

Step by step

  1. Only the ones with more than 1 order.
    SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id HAVING COUNT(*) > 1
  2. No join.
    SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id HAVING COUNT(*) > 1
  3. A rule about a total: HAVING COUNT(*) > 1.
    SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id HAVING COUNT(*) > 1
  4. GROUP BY, then HAVING.
    SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id HAVING COUNT(*) > 1

Practice

Try each one in your head first, then open the answer.

1. Which customers came back? Show customer_id and how many orders they placed, for customers with more than one order.
SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id HAVING COUNT(*) > 1
2. Which customers spent more than 100 in total? Show customer_id and spend.
SELECT customer_id, SUM(revenue) AS spend FROM orders GROUP BY customer_id HAVING SUM(revenue) > 100
3. Which products were bought more than once? Show product and orders.
SELECT product, COUNT(*) AS orders FROM orders GROUP BY product HAVING COUNT(*) > 1
4. Which stores had more than 3 orders? Show store and orders.
SELECT store, COUNT(*) AS orders FROM orders GROUP BY store HAVING COUNT(*) > 3
5. Which customers bought from more than one store? Show customer_id and stores.
SELECT customer_id, COUNT(DISTINCT store) AS stores FROM orders GROUP BY customer_id HAVING COUNT(DISTINCT store) > 1

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed