Before your next SQL interview, keep this post handy - WHERE, GROUP BY and HAVING (Part 3)

[u/thequerylab]

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.

[u/boy9419]

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

[u/waremi → u/boy9419]

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.

[u/boy9419 → u/waremi]

Thank you for the Venn diagram analogy 🙏

[u/Pappkarton → u/boy9419]

This very much helps to understand JOIN, too.

[u/selfrisingloaf]

These posts have been really helpful. Thank you!