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
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;
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.
Share Share Tweet