Q: HAVING without GROUP BY

HAVING is valid without GROUP BY - the entire table is treated as a single group. AVG(salary) = (80000+90000+70000)/3 = 80000. Since 80000 > 75000, the HAVING condition passes and the single-row result is returned. If the average were below 75000, the query would return 0 rows.

Question 1

Filtering by group size

Accepted Answer

After grouping, Alice has 3 orders, Bob has 1, Carol has 1. HAVING COUNT(*) >= 2 keeps only groups with 2 or more orders. Only Alice qualifies. This pattern - filter by group size - is one of the most common uses of HAVING.

Question 2

Which group survives the filter?

Accepted Answer

GROUP BY customer produces Alice (100+300=400) and Bob (200+50=250). HAVING SUM(amount) > 300 then filters those groups — only Alice's total of 400 passes. Bob's 250 is excluded. HAVING is evaluated after GROUP BY, unlike WHERE which runs before.

Question 3

When does the filter run?

Accepted Answer

The logical order in SQL is: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. WHERE runs before aggregation, so it cannot reference aggregate results like SUM() or COUNT(). HAVING runs after aggregation and is specifically designed to filter on aggregate values. Using WHERE where HAVING is needed, or vice versa, causes either a syntax error or incorrect results.

Question 4

What does HAVING filter?

Accepted Answer

SQL executes WHERE before GROUP BY, so WHERE can only see raw column values - not aggregate results. HAVING runs after GROUP BY, so it can filter on things like HAVING COUNT(*) > 5 or HAVING SUM(amount) > 1000. A common mistake is writing WHERE COUNT(*) > 5, which fails because COUNT is not yet computed at that stage.

Question 5

Two filters, one query

Accepted Answer

Step 1 - WHERE filters to shipped rows only: Alice 100, Alice 200, Bob 400. Alice's pending order (500) is excluded. Step 2 - GROUP BY customer: Alice = 300, Bob = 400. Step 3 - HAVING SUM > 100: both 300 and 400 pass. Both customers are returned.

Question 6

Every member must qualify

Accepted Answer

Within each dept, MIN(salary) is: Eng = 60000, Sales = 70000, HR = 50000. HAVING MIN(salary) >= 60000 keeps only depts where the lowest salary is at least 60000. HR's minimum (50000) fails the condition. Eng and Sales both pass.

Question 7

Can HAVING see a SELECT alias?

Accepted Answer

In standard SQL, HAVING must reference the aggregate expression directly, not the alias defined in SELECT. The alias 'cnt' is not visible to HAVING because SELECT is evaluated after HAVING in the logical order. Some databases (like MySQL) allow aliases in HAVING as an extension, but repeating the expression is the portable approach.

Question 8

Capping a category by its peak

Accepted Answer

MAX(price) per category: Books = 18, Toys = 45, Food = 5. HAVING MAX(price) < 40 keeps only categories where even the most expensive item is below 40. Toys has a max of 45, so it is excluded. Both Books (max 18) and Food (max 5) pass the filter.

Question 9

HAVING without GROUP BY

Accepted Answer

HAVING is valid without GROUP BY - the entire table is treated as a single group. AVG(salary) = (80000+90000+70000)/3 = 80000. Since 80000 > 75000, the HAVING condition passes and the single-row result is returned. If the average were below 75000, the query would return 0 rows.

Question 10

Full GROUP BY pipeline

Accepted Answer

Step 1 - WHERE keeps only West rows: Bob 200, Carol 900, Bob 400. Alice (East) is excluded. Step 2 - GROUP BY rep: Bob = 600, Carol = 900. Step 3 - HAVING SUM > 300: both pass. Step 4 - ORDER BY total DESC: Carol (900) before Bob (600).

Filtering Groups