This tutorial explains the difference between WHERE and HAVING clause in GROUP BY in SQL.
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.
1. First WHERE condition - WHERE ProductCode <= 100 executes. It removes all those cases wherein value of product code is greater than 100.
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
Create Sample Data in SAS
data temp;SQL Code : Subsetting Data
input ID Sale ProductCode;
cards;
1 2500 35
1 3000 75
2 5000 65
2 3500 125
3 2500 25
3 2000 255
;
run;
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.
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
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