Полезная информация

next up previous contents index
Next: Using HAVING Up: SQL Aggregates Previous: Aggregates

Using GROUP BY

 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.

  
Figure: Aggregate with GROUP BY
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...t~22
\par PA~~~\vert~22\vert~22\vert~22
\par (4~rows)\end{list}\par
\end{figure}

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.


next up previous contents index
Next: Using HAVING Up: SQL Aggregates Previous: Aggregates
Bruce Momjian
1999-11-21