BBampaa

Lessons › SQL › Go further in SQL: Business questions

SQL lesson

What share of revenue does each store bring?

What share of all revenue does each store bring? A share is a part divided by the whole: each store's total divided by the grand total. The grand total has to be worked out first, so it's a subquery. Keep shares as decimals: 0.43, not 43.

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

Example

A part divided by the whole

SELECT store,
       1.0 * SUM(revenue) / (SELECT SUM(revenue) FROM orders) AS share
FROM orders
GROUP BY store

Result:

storeshare
Abuja0.201
Accra0.433
Lagos0.365

Step by step

  1. The share as a decimal.
    SELECT store, 1.0 * SUM(revenue) / (SELECT SUM(revenue) FROM orders) AS share FROM orders GROUP BY store
  2. No join.
    SELECT store, 1.0 * SUM(revenue) / (SELECT SUM(revenue) FROM orders) AS share FROM orders GROUP BY store
  3. No WHERE.
    SELECT store, 1.0 * SUM(revenue) / (SELECT SUM(revenue) FROM orders) AS share FROM orders GROUP BY store
  4. (SELECT SUM(revenue) FROM orders) gives the grand total.
    SELECT store, 1.0 * SUM(revenue) / (SELECT SUM(revenue) FROM orders) AS share FROM orders GROUP BY store

Practice

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

1. What share of all revenue does each store bring? Show store and share, as a decimal.
SELECT store, 1.0 * SUM(revenue) / (SELECT SUM(revenue) FROM orders) AS share FROM orders GROUP BY store
2. What share of all orders does each store have? Show store and order_share, as a decimal.
SELECT store, 1.0 * COUNT(*) / (SELECT COUNT(*) FROM orders) AS order_share FROM orders GROUP BY store
3. What share of all revenue does each product bring? Show product and share, as a decimal.
SELECT product, 1.0 * SUM(revenue) / (SELECT SUM(revenue) FROM orders) AS share FROM orders GROUP BY product
4. What share of all revenue came from orders with a coupon? Show one number, coupon_share, as a decimal.
SELECT 1.0 * SUM(revenue) / (SELECT SUM(revenue) FROM orders) AS coupon_share FROM orders WHERE coupon IS NOT NULL
5. Which stores bring in more than a third of all revenue? Show store and revenue.
SELECT store, SUM(revenue) AS revenue FROM orders GROUP BY store HAVING SUM(revenue) > (SELECT SUM(revenue) FROM orders) / 3.0

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