7.6 Aggregating Data

You have seen how individual records can be inserted, updated, and selected. In many cases, it is necessary to perform computations with more than just one record involved.

Aggregation functions can be used. PostgreSQL provides the standard set of aggregation functions that are also provided by most other relational databases. In this section you will learn to use aggregation functions efficiently and you will see how multiple records can be treated.

7.6.1 COUNT

For counting the records in a set of data, SQL provides a function called COUNT. With the help of COUNT, it is an easy task to find out how many records are in a table or how many records satisfy certain conditions.

Take a look at an example:

 phpbook=# SELECT COUNT(*) FROM course;  count -------      4 (1 row) 

The table called course contains four records. If you want to retrieve only those where the name of the training course is PostgreSQL for Tcl Programmers, you have to add a WHERE clause as shown in the next listing:

 phpbook=# SELECT COUNT(*) FROM course WHERE course='PostgreSQL for Tcl Programmers';  count -------      2 (1 row) 

Two records are returned, which is correct.

7.6.2 AVG

The AVG (or average) function can be used to compute the arithmetic mean of a list of values. To show you how the function works, you can create a table and insert some records into it:

 phpbook=# CREATE TABLE mydata(data int4); CREATE phpbook=# INSERT INTO mydata VALUES(23); INSERT 24808 1 phpbook=# INSERT INTO mydata VALUES(54); INSERT 24809 1 

In the next step, you try to compute the arithmetic mean of the records in the table:

 phpbook=# SELECT AVG(data) FROM mydata;       avg ---------------  38.5000000000 (1 row) 

PostgreSQL will display the result on the screen.

7.6.3 MAX and MIN

MAX and MIN can be used just like all other aggregation functions because the syntax is the same as you have seen when dealing with AVG and COUNT.

Let's look at an example:

 phpbook=# SELECT MAX(data) FROM mydata;  max -----   54 (1 row) 

The maximum value in the column has been returned. The MIN function works the same way as the MAX function:

 phpbook=# SELECT MIN(data) FROM mydata;  min -----   23 (1 row) 

7.6.4 SUM

To compute the sum of a list of values, use the SUM function. Just like all other aggregation functions, SUM processes the list of values or the column passed to it record by record. With the SUM function, PostgreSQL will add all values and return the result:

 phpbook=# SELECT SUM(data) FROM mydata;  sum -----   77 (1 row) 

The sum of all values in the example is 77.

7.6.5 GROUP BY

Recall the content of table course:

 phpbook=# SELECT * FROM course;  student_id |             course ------------+--------------------------------           1 | PostgreSQL for Beginners           1 | PostgreSQL for C Programmers           1 | PostgreSQL for Tcl Programmers           2 | PostgreSQL for Tcl Programmers (4 rows) 

Four records are in the table. In this example, the number of student_ids is computed:

 phpbook=# SELECT COUNT(student_id) FROM course;  count -------      4 (1 row) 

Let's try an additional query. In the next example you want to display the name of the training course as well:

 phpbook=# SELECT COUNT(student_id), course FROM course; ERROR:  Attribute course.course must be GROUPed or used in an aggregate function 

This does not work because PostgreSQL does not know which name to display. To count the number of people attending a certain training course, it is necessary to add a GROUP BY statement to the query:

 phpbook=# SELECT COUNT(student_id), course FROM course GROUP BY course;  count |             course -------+--------------------------------      1 | PostgreSQL for Beginners      1 | PostgreSQL for C Programmers      2 | PostgreSQL for Tcl Programmers (3 rows) 

As you can see, one person is attending the beginners' course, one person attends the C training course, and two people are in favor of the Tcl training course. Every column you want to display has to be added to the GROUP BY statement because otherwise PostgreSQL will not know which data should be displayed on the screen. The same applies to all other aggregation functions.

7.6.6 HAVING

In the previous section about GROUP BY, you have seen how to perform simple aggregations. In many cases it is necessary to process the result of an aggregation and to perform additional operations. HAVING clauses can be used. A HAVING clause is like an ordinary WHERE clause except that it does not operate on the original data but on the result of an aggregation function. This way, it is possible to perform many operations with just one SQL statement.

Here's an example. Imagine that you want to retrieve all training courses where exactly one student is going to participate:

 SELECT COUNT(student_id), course         FROM course         GROUP by course         HAVING COUNT(student_id)=1; 

You can perform the same query you have already seen when counting the people attending a certain training course, but this time you have to add a HAVING clause that tells PostgreSQL to select only these values. The result will contain two records:

  count |            course -------+------------------------------      1 | PostgreSQL for Beginners      1 | PostgreSQL for C Programmers (2 rows) 

As you can see, the entry for the Tcl training course is missing because two people will attend the training course. If you need more than just one condition in the HAVING clause, it can be added just as you would do in a WHERE clause. In the next example you can see that a second condition has been added to the HAVING clause:

 SELECT COUNT(student_id), course         FROM course         GROUP by course         HAVING COUNT(student_id)=1                 AND COUNT(student_id) > 0; 

The result is still the same because no record in the table is lower or equal than zero:

  count |            course -------+------------------------------      1 | PostgreSQL for Beginners      1 | PostgreSQL for C Programmers (2 rows) 

HAVING clauses are a comfortable way to solve many common problems. However, if you have to perform more than one operation on the result of an aggregation function, it might be more useful to work with temporary tables:

 SELECT COUNT(student_id), course INTO TEMP TABLE tmp_tab         FROM course         GROUP by course; 

After creating the temporary table, it is an easy task to retrieve values:

 phpbook=# SELECT * FROM tmp_tab;  count |             course -------+--------------------------------      1 | PostgreSQL for Beginners      1 | PostgreSQL for C Programmers      2 | PostgreSQL for Tcl Programmers (3 rows) 

Let's select all records where the number of people attending the training course is equal to 1:

 phpbook=# SELECT * FROM tmp_tab WHERE count=1;  count |            course -------+------------------------------      1 | PostgreSQL for Beginners      1 | PostgreSQL for C Programmers (2 rows) 

The result of the query is equal to the one you have achieved when using the HAVING clause.