Counting the number of rows returned with COUNT and the WHERE clause.
Being a little more specific using WHERE.
As we did before with our SELECT * query, we can filter specific rows in our table while using our COUNT(*) function. This will allow us to only count the rows in the table where the specified condition in the WHERE clause is met.
Why would we do this?
Sometimes it's useful to know how many rows in total we have in our table but the real power comes with being able to count specific things in our data.
Like if we wanted to see how many of the ancient Wonders of the World would have been found in Babylon:
SELECT COUNT(*)
FROM WondersOfTheWorld
WHERE Located='Babylon';And the answer would, of course, be 1 (The Hanging Gardens of Babylon).
Quiz Time.
The Question.
There is a census happening far away in Asgard. So we need to count how many Asgardian superheroes we have hanging out with us.Just the number of them, not all of their details. So use COUNT and the WHERE clause to filter out those pesky humans.
The Data.
The Editor.
Show Answer
The Answer.
Correct SQL:
SELECT COUNT(*) FROM characters WHERE nationality='Asgardian';