07 - Having vs Where in MySQL

 https://youtu.be/dCNjUOc1cBY?si=98wPr8SDYuRBXsGn

TOTAL: 3.45 mins

-- Having vs Where

-- Both were created to filter rows of data, but they filter 2 separate things

-- Where is going to filters rows based off columns of data

-- Having is going to filter rows based off aggregated columns when grouped

SELECT gender, AVG(age)

FROM employee_demographics

GROUP BY gender

;

-- let's try to filter on the avg age using where

-- đã nhóm = GROUP BY rồi là không dùng WHERE để lọc => đó là lí do HAVING ra đời

-- HAVING -> lọc trên kết quả đã nhóm

SELECT gender, AVG(age)

FROM employee_demographics

WHERE AVG(age) > 40

GROUP BY gender

;

-- this doesn't work because of order of operations. On the backend Where comes before the group by. So you can't filter on data that hasn't been grouped yet

-- this is why Having was created

SELECT gender, AVG(age)

FROM employee_demographics

GROUP BY gender

HAVING AVG(age) > 40

;

SELECT gender, AVG(age) as AVG_age

FROM employee_demographics

GROUP BY gender

HAVING AVG_age > 40

;

-- 03:00

SELECT occupation, AVG(salary)

FROM employee_salary

WHERE occupation LIKE '%manager%'

GROUP BY occupation

HAVING AVG(salary) > 75000

;