WHERE and HAVING in SQL

Where

The where clause allows you to select rows from the table based on specific conditions.

select * from Person
where Name like "D%";

This query selects all people whose names start with the letter D.

If you perform a group by and want to filter the data after grouping, you need to use the having clause.

Having

select Genre, avg(Age) as AvgAge
from Person
group by Genre
having AvgAge > 20

This query selects the genders, associating them with the average age related to that gender, filtering the results for genders that have an average age greater than 20 years.

Pandas

Where

In pandas, we can implement the where condition in two ways:

#explicit where
person.where(person["Name"].str.startswith("D"))
 
#implicit where
person[person["Name"].str.startswith("D")]

Note that person["Name"] is a Pandas Series, and because of that we have to access the underlying string in the .str attribute.

We can implement the second example like this:

df = person.groupby("Genre")['Age'].mean().reset_index(name="AvgAge")
df = df[df["AvgAge"] > 20]

Note that there is no concept of HAVING in pandas, but we just apply a where condition after the groupby operation.