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.
Schema reminder
DB schema

Expected Results

count
9

Your Answer Hint Help Save Run Query

 

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.

Try investigating the SQL COUNT function

Keyboard shortcuts:


Other hints: