Filtering data using groupby in sql

select
billingcity,
round(avg(total),2)
from Invoice
where billingcity is not null
GROUP by billingcity
order by  billingcity;

select
billingcity,
round(avg(total),2) as AvgInvoice
from Invoice
where billingcity is not null and billingcity like 'L%'
GROUP by billingcity
order by  billingcity;

we can only use where if the condition is applied to non aggregated field such as billingcity ,in case we like to impose a condition on the aggregated field such as total we have to use having clause,and having will always come after group by clause

select
billingcity,
round(avg(total),2) as AvgInvoice
from Invoice
where billingcity is not null and billingcity like 'L%'
GROUP by billingcity
having AvgInvoice>5
order by  billingcity;

we can group by multiple fields instead of just one ,to do that we have to add the field as non aggregated field in the select statement,and then add the second group by field after the first separated by a comma

select
billingcountry,
billingcity,
round(avg(total),2) as AvgInvoice
from Invoice
where billingcity is not null

GROUP by billingcountry,billingcity
having AvgInvoice>1
order by  billingcountry,AvgInvoice ASC;