Count the number of facilities
Question
For our first foray into aggregates, we're going to stick to something simple. We want to know how many facilities exist - simply produce a total count.
Your Answer
Answers and Discussion Show
select count(*) from cd.facilities;
Aggregation starts out pretty simply! The SQL above selects everything from our facilities table, and then counts the number of rows in the result set. The count function has a variety of uses:
- COUNT(*) simply returns the number of rows
- COUNT(address) counts the number of non-null addresses in the result set.
- Finally, COUNT(DISTINCT address) counts the number of different addresses in the facilities table.
The basic idea of an aggregate function is that it takes in a column of data, performs some function upon it, and outputs a scalar (single) value. There are a bunch more aggregation functions, including MAX, MIN, SUM, and AVG. These all do pretty much what you'd expect from their names :-).
One aspect of aggregate functions that people often find confusing is in queries like the below:
select facid, count(*) from cd.facilities
Try it out, and you'll find that it doesn't work. This is because count(*) wants to collapse the facilities table into a single value - unfortunately, it can't do that, because there's a lot of different facids in cd.facilities - Postgres doesn't know which facid to pair the count with.
Instead, if you wanted a query that returns all the facids along with a count on each row, you can break the aggregation out into a subquery as below:
select facid, (select count(*) from cd.facilities) from cd.facilities
When we have a subquery that returns a scalar value like this, Postgres knows to simply repeat the value for every row in cd.facilities.