Should I group by the name or the id?

Group by the primary key, and include the name so the output is readable. Two workspaces could share a label. Their ids cannot.

Can I ORDER BY an aggregate alias?

In SQLite, yes. ORDER BY revenue_cents works when revenue_cents is the alias of a SUM. Other engines are similar, but a repeated expression is always safe.

GROUP BY and HAVING

Aggregation changes the grain of the data. A line item becomes a category. An invoice becomes a workspace. HAVING is the filter you apply after that change.

Decide the grain first

If the question says revenue by category, each output row is a category, so every selected column is either the category or an aggregate. If it says customers with two orders, each output row is a customer and HAVING COUNT(*) >= 2 is the threshold.

WHERE then GROUP BY then HAVING

WHERE drops raw rows, such as cancelled orders. GROUP BY builds the groups. HAVING drops groups. Putting a count in WHERE is a syntax error because the count does not exist yet.

Example

SELECT email, COUNT(*) AS lead_count
FROM leads
GROUP BY email
HAVING COUNT(*) > 1;

Duplicate detection is a group and a threshold. The email is the grain.

Practice

EasyE-commerce
Revenue by category
Sum recognized revenue for each product category.
EasyE-commerce
Customers with repeat orders
Find customers with at least two orders that were not cancelled.
MediumSaaS
Invoiced amount by workspace
Total every invoice issued to each workspace.
MediumUniversity
Enrollment counts by course
Count students enrolled in each course.
MediumLogistics
Active shipments per warehouse
Count shipments per warehouse, excluding cancelled freight.
HardE-commerce
Monthly succeeded revenue
Sum succeeded payments by calendar month.
MediumMarketing
Duplicate lead emails
Find email addresses that were captured more than once.
HardE-commerce
Rank products by revenue
Rank products by recognized revenue.

Common mistakes

Selecting a column that is neither grouped nor aggregated.
Counting joined rows and thinking you counted the parent.
Using WHERE COUNT(*) > 1.
Averaging historical salaries as if they were all current.

FAQ

Should I group by the name or the id?: Group by the primary key, and include the name so the output is readable. Two workspaces could share a label. Their ids cannot.
Can I ORDER BY an aggregate alias?: In SQLite, yes. ORDER BY revenue_cents works when revenue_cents is the alias of a SUM. Other engines are similar, but a repeated expression is always safe.

Related practice

Start solving SQL challenges today.

Open Revenue by category and check the result in the browser. An account is optional.

Start practicing free