#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

WHERE vs HAVING in MySQL: What's the Difference?

This query looks reasonable and fails:

SELECT city, SUM(amount) AS revenue
FROM orders
WHERE SUM(amount) > 5000
GROUP BY city;
ERROR 1111 (HY000): Invalid use of group function

The error is the whole difference between the two clauses in one line. WHERE filters individual rows before they are grouped, so at that moment there is no SUM() to test. HAVING filters groups after GROUP BY has built them, so it can test aggregates. Move the condition to HAVING and the query works.

Sample data

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  city     VARCHAR(15),
  status   VARCHAR(10),
  amount   DECIMAL(10,2)
);
INSERT INTO orders VALUES
 (1,'Kanpur','delivered',2400),  (2,'Kanpur','delivered',3100),  (3,'Kanpur','returned',1800),
 (4,'Agra','delivered',1500),    (5,'Agra','delivered',900),     (9,'Agra','returned',700),
 (6,'Varanasi','delivered',4200),(7,'Varanasi','delivered',2600),(8,'Varanasi','returned',5000);

HAVING: filtering groups

SELECT city, SUM(amount) AS revenue
FROM orders
GROUP BY city
HAVING SUM(amount) > 5000
ORDER BY city;
cityrevenue
Kanpur7300.00
Varanasi11800.00

Agra's total is 3,100, so its group is removed.

Using both: a question that needs each clause

"Which cities have more than ₹5,000 of delivered sales?" Returned orders must be excluded row by row (a WHERE job), and then the city totals tested (a HAVING job). First, what WHERE alone produces:

SELECT city, SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY city
ORDER BY city;
cityrevenue
Agra2400.00
Kanpur5500.00
Varanasi6800.00

Now add the group filter:

SELECT city, SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY city
HAVING revenue > 5000
ORDER BY city;
cityrevenue
Kanpur5500.00
Varanasi6800.00

Notice how Varanasi's total dropped from 11,800 to 6,800: the ₹5,000 return was removed by WHERE before summing. If you had tried to remove returns in HAVING, it would be too late; the returned amount is already inside the sum.

The order MySQL works in

StepClauseWorks on
1FROM / JOINBuilds the rows
2WHEREKeeps or drops each row
3GROUP BYCollapses rows into groups
4HAVINGKeeps or drops each group
5SELECTComputes output columns and aliases
6ORDER BY, LIMITSorts and trims the result

This is the logical order (the optimizer may do things differently internally, but the result must be as if it followed these steps). It explains the rules below.

Side-by-side summary

WHEREHAVING
FiltersRowsGroups (or the single group of the whole table)
RunsBefore GROUP BYAfter GROUP BY
Aggregates such as SUM(), COUNT()Not allowed (error 1111)Allowed
Select-list aliasesNot allowed (error 1054)Allowed in MySQL
Can use indexes to avoid reading rowsYesNo, groups are built first
Works in UPDATE / DELETEYesNo

MySQL-specific behaviour

The performance mistake: filtering rows in HAVING

MySQL accepts a non-aggregate condition in HAVING when the column is in GROUP BY, and these two queries return the same result:

SELECT city, SUM(amount) FROM orders GROUP BY city HAVING city <> 'Agra';   -- slower pattern
SELECT city, SUM(amount) FROM orders WHERE city <> 'Agra' GROUP BY city;    -- better
citySUM(amount)
Kanpur7300.00
Varanasi11800.00

The first version reads and groups Agra's rows only to throw the group away; the second never groups them, and can use an index on city. On nine rows nobody notices; on a crore rows it matters. Rule: if a condition can be checked on a single row, put it in WHERE.

A typical interview question

"Show cities with at least two orders above ₹1,000." Row condition (amount > 1000) in WHERE, group condition (COUNT(*) >= 2) in HAVING:

SELECT city, COUNT(*) AS orders
FROM orders
WHERE amount > 1000
GROUP BY city
HAVING COUNT(*) >= 2
ORDER BY city;
cityorders
Kanpur3
Varanasi3

Agra has only one order above ₹1,000 and drops out. More grouping practice is in the GROUP BY clause explained and aggregate functions COUNT, SUM and AVG; row filtering in depth is in the WHERE clause with examples.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →