BBampaa

Lessons › SQL › Go further in SQL: Business questions

SQL lesson

What's the revenue for each customer city?

What's the revenue for each customer city? The answer needs a column that isn't in orders: the city is in customers. When a heading you pictured lives in another table, join first, then group.

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

Example

Join first, then group

SELECT c.city, SUM(o.revenue) AS revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.city

Result:

cityrevenue
Abuja275
Accra245
Lagos299

Step by step

  1. Two columns: the city and its total.
    SELECT c.city, SUM(o.revenue) AS revenue FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY c.city
  2. Both tables have customer_id, so that's how they match.
    SELECT c.city, SUM(o.revenue) AS revenue FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY c.city
  3. No WHERE needed.
    SELECT c.city, SUM(o.revenue) AS revenue FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY c.city
  4. A join, then GROUP BY c.city.
    SELECT c.city, SUM(o.revenue) AS revenue FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY c.city

Practice

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

1. What's the revenue for each customer city? Show city and revenue.
SELECT c.city, SUM(o.revenue) AS revenue FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY c.city
2. What's the average order value for each customer city? Show city and avg_order.
SELECT c.city, AVG(o.revenue) AS avg_order FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY c.city
3. How many orders came from each customer city? Show city and orders.
SELECT c.city, COUNT(*) AS orders FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY c.city
4. What's each customer's total spend, by name, biggest first? Show name and spend.
SELECT c.name, SUM(o.revenue) AS spend FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY c.name ORDER BY spend DESC
5. Which customer cities brought in more than 250? Show city and revenue.
SELECT c.city, SUM(o.revenue) AS revenue FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY c.city HAVING SUM(o.revenue) > 250

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