Difference between WHERE and HAVING Clause in GROUP BY

Live Online Training : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects
- Scenario Based Questions
- Job Placement Assistance
- Get 10% off till Oct 26, 2017
- Batch starts from October 28, 2017

This tutorial explains the difference between WHERE and HAVING clause in GROUP BY in SQL.

Key Difference
The WHERE condition is applied before the grouping occurs. Whereas, the HAVING condition is applied after the grouping occurs.
Sample Data
SQL : WHERE vs. HAVING
Task

First we need to filter out all the product codes having value greater than 100 and then sum up sale by ID. Then keep only those IDs having sum of sales less than or equal to 5000.

Create Sample Data in SAS
data temp;
input ID Sale ProductCode;
cards;
1 2500 35
1 3000 75
2 5000 65
2 3500 125
3 2500 25
3 2000 255
;
run;
SQL Code : Subsetting Data 
proc sql;
select ID, sum(Sale) as total_sale
from temp
where ProductCode <= 100
group by ID
having total_sale <= 5000;
quit;
Output Data
How it works -

1. First WHERE condition - WHERE ProductCode <= 100 executes. It removes all those cases wherein value of product code is greater than 100.
2. Then sum up sale by group ID (after excluding the cases wherein productcode > 100)
3. HAVING condition executes at last. It filters out all the IDs having sum of sale greater than 5000.

Proc SQL Tutorials : 15 Proc SQL Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

1 Response to "Difference between WHERE and HAVING Clause in GROUP BY"

  1. I'm still new at SQL, this seems a bit advance for me but thank you for posting. I'm subscribing your blog and visit it every once in a while. I am only self-studying so im taking it slowly. Thank you very much for the post.

    ReplyDelete

Next → ← Prev