an example using HAVING
SELECT course, Count (*)
FROM students
GROUP BY course
HAVING count (*) > 10
;
this filters groups and not rows and only courses with more than 10 students are returned.
like ;
Select courses, count (*) As total _students
From students
Group By course
;
a tiny clause but really helpful when reading the output because readable outputs matter when sharing results with others.
an example using WHERE;
Select *
From Students
WHERE age > 20
;
this is just general students before grouping them and it returns all students above 20 years old
Day 2 of my Data Analysis journey. I'm now starting to feel like an analyst because apart from the course, this whole learning enhances your critical thinking. This is what I learned 🧵
Where vs Having
WHERE Clause; filters individual rows before grouping and it works on column values.
HAVING Clause; filters groups after grouping and it works on aggregate values.
An example of using Count;
Select course, Count (*)
From students
Group By Course
;
this answers the question " how many students are enrolled in each course? "
When you use Group By, you almost always pair it with aggregate functions. these are;
Avg( ) to calculates the average
Min( ) to calculate the smallest value
Max( ) to calculate the largest value
Count ( ) to count the number of values
Group By and Order By
Group By:
- groups together rows that have the same value in one or more columns.
- so instead of looking for individual records, you start analyzing categories.
WHERE name LIKE 'a___'
name starts with an A and has three place value holders, not more, not less.
but if we combine the _ with % like ;
WHERE name LIKE 'a___%'
means the name starts with an A, has three place value holders and anything after that.