Count vs Select Count (Subqueries P3)

Q&A DQ Courses
#1

In this example can I confirm that COUNT counts every row in the main table while SELECT COUNT counts every row that is filtered using WHERE? Thank you!

SELECT CAST(COUNT(*) as float)/CAST((SELECT COUNT(*) from recent_grads) as float) proportion_abv_avg from recent_grads
WHERE ShareWomen > 
(SELECT AVG(ShareWomen) from recent_grads)
#2

I have found the answer, just fyi for everyone

CAST(COUNT(*) as float) - The number of rows with the WHERE filter

CAST((SELECT COUNT(*) from recent_grads) as float) - A subquery which counts the total number of row in the table, not affected by the WHERE filter

2 Likes