Cacher is the code snippet organizer for pro developers

SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price
FROM PC
GROUP BY model
HAVING AVG(price) < 800;


#NOTICE HOW AVG (PRICE) IS IN SELECT PART AND THEN REFERENCED IN HAVING


SELECT MAKER,TYPE,COUNT(DISTINCT TYPE),COUNT(DISTINCT MODEL) 
FROM PRODUCT GROUP BY MAKER HAVING COUNT(DISTINCT TYPE)=1 AND COUNT(DISTINCT MODEL)>1
#Wrong because type is not avaiable after grouping.

SELECT MAKER,COUNT(DISTINCT TYPE),COUNT(DISTINCT MODEL) 
FROM PRODUCT GROUP BY MAKER HAVING COUNT(DISTINCT TYPE)=1 AND COUNT(DISTINCT MODEL)>1
#This works as

#############################################################################
# after groupby you have access to columns which you used in grouping but not other 
columns. Other columns can be accessed through aggregate functions.
############################################################################
if you use column names and aggregate functions together without grouping it will 
give you an error
You can use them together using "aggfn() over(partion by column)"
#############################################################################
#WHEN YOU WANT TO SELECT ITEMS ONLY FROM A GIVEN LIST
P.MAKER IN (SELECT MAKER FROM A1)



######difference between where and having 

having is applied on aggregate functions
where is on rows