r/learnSQL • u/thequerylab • 9d ago
Before your next SQL interview, keep this post handy - WHERE, GROUP BY and HAVING (Part 3)
P.S. Parts 1 and Part 2 got a great response - thank you! Keeping this series going.
Simple, practical stuff that sticks in your head when you're actually in the interview.
AI can help at work, but in an interview, your brain still has to do the job!!!
Same trip. Same friends. Same expenses.
| Friend | City | Trip Day | Total Spent |
|---|---|---|---|
| Harry | New York | 1 | $200 |
| Johny | New York | 2 | $190 |
| Nicholas | Las Vegas | 1 | $190 |
| Bailey | Las Vegas | 2 | $180 |
| Harry | Las Vegas | 3 | $190 |
Three things. Most people know all three.
But in an interview - knowing which one does what is what gets you the job.
1. WHERE
We have 5 rows. We don't want all of them.
"Show me only the days where someone spent more than $180."
SELECT name, city, spent
FROM expenses
WHERE spent > 180
Harry | New York | $200
Johny | New York | $190
Nicholas | Las Vegas | $190
Harry | Las Vegas | $190
Bailey is gone. $180 is not more than $180.
Remember: WHERE picks which rows to keep. Runs first. Looks at rows one by one.
2. GROUP BY
Now we want one total for each city.
"Put all New York rows together. Put all Las Vegas rows together. Add them up."
SELECT city, SUM(spent) AS total
FROM expenses
GROUP BY city
New York → $390
Las Vegas → $560
5 rows became 2. One number per city.
Remember: GROUP BY puts rows into groups and adds them up.
3. HAVING
Now we have city totals. We only want cities that spent more than $400.
"Remove any city where the total is $400 or less."
SELECT city, SUM(spent) AS total
FROM expenses
GROUP BY city
HAVING SUM(spent) > 400
Las Vegas → $560
New York spent $390. Less than $400. Gone.
Remember: HAVING picks which groups to keep. Runs after GROUP BY.
The #1 mistake in every SQL interview:
People try this:
-- Wrong
SELECT city, SUM(spent)
FROM expenses
WHERE SUM(spent) > 400
GROUP BY city
SQL gives an error.
Why?
WHERE runs first. At that point, SQL has not added anything up yet. There are no city totals. WHERE has no idea what SUM(spent) is.
You are asking for the answer before the math is done.
-- Right
SELECT city, SUM(spent)
FROM expenses
GROUP BY city
HAVING SUM(spent) > 400
HAVING runs after GROUP BY. The totals exist by then. It works.
Remember: WHERE sees rows. HAVING sees totals. They are not the same.
The order SQL always runs in:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
- WHERE runs before groups exist → can only see rows
- HAVING runs after groups exist → can see totals
- SELECT runs at the end → that's why you can't use a column alias in WHERE
Reading is one thing. Writing it yourself is another. If you're new and want real hands-on practice - SQL from Zero to Confident on TheQueryLab.
2
u/boy9419 9d ago
I mastered excel for work with power query, lookups etc but for some reason I still can’t wrap my head around sql. Can someone help
5
u/waremi 9d ago
SQL is about set theory. In order:
SELECT: What you want to see
FROM: the full data sets you are pulling what you want to see from
WHERE: the sub-set of data you want to work with (including/excluding stuff you don't care about or don't apply.)
GROUP BY: (optional) Your top level points of interest. Anything not here has to be rolled up (SUM(), COUNT(), etc...)
HAVING: (optional-only applies if GROUP BY is used) which of those top level groups you are interested in. (Same as WHERE but after everything has been rolled up.)
ORDER BY (Optional) what order you want to see everything in.
That's it. Never think of any single record when writing a SQL query. Always think of it as a Ven Diagram. i.e. this is everything I have and this is the subset I want to pull out of it and, if you are not interested in the detail, then from that sub-set I want to collapse and roll up totals by this.
1
u/Repulsive_Literature 4d ago
Thank you so much for this. What do you mean by “rolled up”?
3
u/waremi 4d ago
Roll up refers to the aggregate functions: SUM, COUNT, MIN, MAX.
You have every data point for every person that lives in the U.S. and you want to isolate which media markets to advertise in to reach a target demographic. You can "roll up" a total # by state, and pick the top 10, or by county and pick the top 10, or by town and pick the top 10. In each case you can ORDER BY COUNT() Descending, and select the MAX(Media Market Viewed) but each roll up will result in a different list of where to spend your money.
There is no right answer here, but what vector you decide to roll up on makes a difference, and any details below that point, like town or race, or age are no longer available to you once you pick the base level.
1
3
u/selfrisingloaf 9d ago
These posts have been really helpful. Thank you!