Simple aggregates return one row as a result. It is
often desirable to apply an aggregate to groups of rows. Queries using
aggregates with GROUP By have the aggregate applied to rows grouped
by another column in the table. For example, SELECT COUNT(*) FROM
friends returns the total number of rows in the table. The query in figure
shows the use of GROUP BY to generate a count of the number of people
in each state. COUNT(*) is not applied to the entire table at once.
With GROUP BY, the table is split up into groups by state, and
COUNT(*) is applied to each group.
The second query shows the minimum, maximum, and average age of the people in each state. It also shows an ORDER BY on the aggregate column. Because the column is the fourth column in the result, you can identify the column by the number 4. Doing ORDER BY avg would have worked too. Aggregates are quite useful. Psql's \da command lists all the aggregates supported by POSTGRESQL.
One reminder on query formatting. You will see in figure I have spread the query across several lines. Whenever you have a SELECT query with FROM, WHERE, and other clauses, it is best to spread the query over multiple lines, putting each clause on a separate line. The queries are much clearer. I have also used appropriate capitalization.
It doesn't make a big difference now, but once you are issuing queries on live, production databases, you will want to double-check the query before executing it. Sloppy query formatting makes the query hard to understand, so you are more likely to issue an erroneous query that could cause you problems. Well formatted queries are easy to understand and double-check, making your job easier.