SQL for Data Analytics · Query Order

Query Order

Query Order11 min

Query Order11 min

A note on this video

The Sales table from the slides is loaded in the box below, so you can run the query from the video and take it apart a piece at a time. Start with SELECT * FROM Sales and the WHERE, then add the GROUP BY, and so on.

One thing the video doesn't say. Many databases, including the one in the videos and the one on this site, are a little more relaxed than the order I show. They let you use an alias in GROUP BY and HAVING too, even though those run before SELECT. The rule to remember is: an alias works anywhere except WHERE.

Run queries here while you watch. When I tell you in the video to open Workbench and try something, use this box. The tables from the video are already loaded. This is for your own practice. It isn't recorded or part of your unit submission.

⌘/Ctrl + Enter
Try it

These two use the Sales table from the video.

The query in the box doesn't run. Fix it so it does. It should show each category with its total quantity, but only categories with a total over 10.
⌘/Ctrl + Enter

WHERE runs before the groups exist, so it can't see a total. There's a clause that runs after GROUP BY, and it can use SUM(Quantity) the same way the SELECT does.

A junior analyst asks you why one of these works and the other doesn't. Both use the same alias, TotalQuantity.

-- Query 1
SELECT Category, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY Category
WHERE TotalQuantity > 10;

-- Query 2
SELECT Category, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY Category
ORDER BY TotalQuantity DESC;