Using GROUP BY

 Simple aggregates return one row as a result. It is often desirable, however, to apply an aggregate to groups of rows. In queries using aggregates with GROUP BY, the aggregate is applied to rows grouped by another column in the table. For example, SELECT COUNT(*) FROM friend returns the total number of rows in the table. The query in Figure  shows the use of GROUP BY to count the number of people in each state. With GROUP BY, the table is split up into groups by state, and COUNT(*) is applied to each group in turn.  

        test=> SELECT state, COUNT(*) 
        test-> FROM friend
        test-> GROUP BY state;
         state | count 
        -------+-------
         MA    |     2
         MD    |     1
         NJ    |     2
         PA    |     1
        (4 rows) 
         
        test=> SELECT state, MIN(age), MAX(age), AVG(age)  
        test-> FROM friend
        test-> GROUP BY state
        test-> ORDER BY 4 DESC;
         state | min | max | avg 
        -------+-----+-----+-----
         MA    |  24 |  33 |  28
         MD    |  27 |  27 |  27
         NJ    |  19 |  25 |  22
         PA    |  22 |  22 |  22
        (4 rows)  

The second query shows the minimum, maximum, and average ages of the people in each state. It also shows an ORDER BY operation carried out on the aggregate column. Because the column is the fourth one in the result, you can identify it by the number 4. Using ORDER BY  avg would have worked as well.

You can GROUP BY more than one column, as shown in Figure .  

        test=> SELECT city, state, COUNT(*)
        test-> FROM friend
        test-> GROUP BY state, city
        test-> ORDER BY 1, 2;
              city       | state | count 
        -----------------+-------+-------
         Boston          | MA    |     1
         Cedar Creek     | MD    |     1
         Ocean City      | NJ    |     2
         Plymouth        | MA    |     1
         Williamsport    | PA    |     1
        (5 rows)  

GROUP BY collects all NULL  values into a single group.