Total Sales per Order Status with SUM and GROUP BY
Return the combined order value for each order status.
The challenge runs on the SQLabHub practice database (SQLite) and uses the orders table. You can solve it in the browser, with nothing to install.
The task
Sum order totals grouped by status.
A correct answer returns 6 rows with 2 columns: status, SUM(total).
Tables used in this challenge
orders 200 rows
id INTEGER (PK) · user_id INTEGER · total REAL · discount REAL · item_count INTEGER · status TEXT · order_date TEXT · shipped_date TEXT · shipping_city TEXT · shipping_country TEXT
| id | user_id | total | discount | item_count | status | order_date | shipped_date | shipping_city | shipping_country |
|---|---|---|---|---|---|---|---|---|---|
The first rows of each table are shown. See how all the tables relate on the database schema page.
Expected output
| status | SUM(total) |
|---|---|
The complete result: 6 rows. This is the output of the model solution on the practice database. The task does not fix a sort order, so your rows may come back in a different order.
What you practise
- Summarising many rows into one value with COUNT, SUM, AVG, MIN or MAX
- Grouping rows with GROUP BY to get one result row per group
Hint and approach
Try the challenge first. Open these only when you need them.
Show the hint
GROUP BY status
Show the step-by-step approach
How the model solution is built, in the order the database evaluates it:
- Read from the
orderstable. - Group the rows by
status, so the result has one row for each value. - For each group, compute
SUM(total).
Model solution
Checking your session…
What to watch for
The sum is taken per group, so cancelled and refunded orders get their own totals instead of being mixed into revenue. To report revenue only, filter those statuses out in WHERE before grouping.
- GROUP BY. Every selected column must either be in GROUP BY or sit inside an aggregate. SQLite accepts other columns and returns an arbitrary value for them; most databases raise an error.
- Aggregate functions. Aggregates skip NULLs. AVG divides by the number of non-NULL values and COUNT(column) can be smaller than COUNT(*).
Related SQL challenges
- Get the Maximum Salary per Department with GROUP BYSimilar levelEasy
- Count Employees in Each DepartmentSimilar levelEasy
- Find the Last Order Date for Each CustomerHarderMedium
- Summarise Each Order: Total Quantity and AmountHarderMedium
- Find the Lowest Price in Each CategorySimilar levelEasy
Same technique, other tables
More practice on this pattern with different columns:
Keep learning
- Lesson: GROUP BY
- Lesson: Aggregate Functions
- All SQL practice challenges, by difficulty and topic
- SQL interview questions with answers
- SQL Playground: run any query on the same database