Aggregates

Table  lists five aggregates.

 

Table: Aggregates

Aggregate	Function
COUNT(*)	count of rows
SUM(colname)	total
MAX(colname)	maximum
MIN(colname)	minimum
AVG(colname)	average

COUNT operates on entire rows; the other four operate on specific columns. Figure  shows examples of aggregate queries.

 

        test=> SELECT * FROM friend ORDER BY firstname;
            firstname    |       lastname       |      city       | state | age 
        -----------------+----------------------+-----------------+-------+-----
         Dean            | Yeager               | Plymouth        | MA    |  24
         Dick            | Gleason              | Ocean City      | NJ    |  19
         Ned             | Millstone            | Cedar Creek     | MD    |  27
         Sandy           | Gleason              | Ocean City      | NJ    |  25
         Sandy           | Weber                | Boston          | MA    |  33
         Victor          | Tabor                | Williamsport    | PA    |  22
        (6 rows) 
         
        test=> SELECT COUNT(*) FROM friend;
         count 
        -------
             6
        (1 row) 
         
        test=> SELECT SUM(age) FROM friend;
         sum 
        -----
         150
        (1 row) 
         
        test=> SELECT MAX(age) FROM friend;
         max 
        -----
          33
        (1 row) 
         
        test=> SELECT MIN(age) FROM friend;
         min 
        -----
          19
        (1 row) 
         
        test=> SELECT AVG(age) FROM friend;
         avg 
        -----
          25
        (1 row)  

Aggregates can be combined with a WHERE clause to produce more complex results. For example, the query SELECT AVG(age) FROM friend WHERE age >= 21 computes the average age of people age 21 or older. This prevents Dick Gleason from being included in the average computation because he is younger than 21. The column label defaults to the name of the aggregate.  You can use AS to change it, as described in Section .

 NULL values are not processed by most aggregates, such as MAX(), SUM(), and AVG(); they are simply ignored. However, if a column contains only NULL values, the result is NULL, not zero. COUNT(*) is different in this respect. It does count NULL values because it looks at entire rows using the asterisk(*). It does not examine individual columns like the other aggregates. To find the COUNT of all non-NULL values in a certain column, use COUNT(colname). To find the number of distinct values in a column, use COUNT(DISTINCT   colname).

Figure  illustrates aggregate handling of NULL values. First, a single row containing a NULL column is used to show aggregates returning NULL results. Two versions of COUNT on a NULL column are shown. Notice that COUNT never returns a NULL value. Then, a single non-NULL row is inserted, and the results shown. Notice the AVG() of 3 and NULL is 3, not 1.5, illustrating the NULL value is not considered in the average computation.   

        
        test=> CREATE TABLE aggtest (col INTEGER);
        CREATE
        test=> INSERT INTO aggtest VALUES (NULL);
        INSERT 19759 1
        test=> SELECT SUM(col) FROM aggtest;
         sum 
        -----
            
        (1 row) 
         
        test=> SELECT MAX(col) FROM aggtest;
         max 
        -----
            
        (1 row) 
         
        test=> SELECT COUNT(*) FROM aggtest;
         count 
        -------
             1
        (1 row) 
         
        test=> SELECT COUNT(col) FROM aggtest;
         count 
        -------
             0
        (1 row) 
         
        test=> INSERT INTO aggtest VALUES (3);
        INSERT 19760 1
        test=> SELECT AVG(col) FROM aggtest;
         avg 
        -----
           3
        (1 row) 
         
        test=> SELECT COUNT(*) FROM aggtest;
         count 
        -------
             2
        (1 row) 
         
        test=> SELECT COUNT(col) FROM aggtest;
         count 
        -------
             1
        (1 row)
         

Psql's \da command lists all of the aggregates supported by POSTGRESQL.