The select statements introduced so far can join, project, and manipulate individual rows. In addition, a select statement can perform aggregation operations over multiple rows. The aggregate functions max, min, count, and sum return the maximum, minimum, number, and sum of the values in a column. Multiple aggregate functions can be applied to the same set of rows by defining more than one column. Only columns that are included by the where clause are considered in the aggreagation.

sqlite> create table animals as
      ....>   select "dog" as name, 4 as legs, 20 as weight union
      ....>   select "cat"        , 4        , 10           union
      ....>   select "ferret"     , 4        , 10           union
      ....>   select "t-rex"      , 2        , 12000        union
      ....>   select "penguin"    , 2        , 10           union
      ....>   select "bird"       , 2        , 6;
    sqlite> select max(legs) from animals;
    4
    sqlite> select sum(weight) from animals;
    12056
    sqlite> select min(legs), max(weight) from animals where name <> "t-rex";
    2|20
    

The distinct keyword ensures that no repeated values in a column are included in the aggregation. Only two distinct values of legs appear in the animals table. The special count(*) syntax counts the number of rows.

sqlite> select count(legs) from animals;
    6
    sqlite> select count(*) from animals;
    6
    sqlite> select count(distinct legs) from animals;
    2
    

Each of these select statements has produced a table with a single row. The group by and having clauses of a select statement are used to partition rows into groups and select only a subset of the groups. Any aggregate functions in the having clause or column description will apply to each group independently, rather than the entire set of rows in the table.

For example, to compute the maximum weight of both a four-legged and a two-legged animal from this table, the first statement below groups together dogs and cats as one group and birds as a separate group. The result indicates that the maximum weight for a two-legged animal is 3 (the bird) and for a four-legged animal is 20 (the dog). The second query lists the values in the legs column for which there are at least two distinct names.

sqlite> select legs, max(weight) from animals group by legs;
    2|12000
    4|20
    sqlite> select weight from animals group by weight having count(*)>1;
    10
    

Multiple columns and full expressions can appear in the group by clause, and groups will be formed for every unique combination of values that result. Typically, the expression used for grouping also appears in the column description, so that it is easy to identify which result row resulted from each group.

sqlite> select max(name) from animals group by legs, weight order by name;
    bird
    dog
    ferret
    penguin
    t-rex
    sqlite> select max(name), legs, weight from animals group by legs, weight
      ....>   having max(weight) < 100;
    bird|2|6
    penguin|2|10
    ferret|4|10
    dog|4|20
    sqlite> select count(*), weight/legs from animals group by weight/legs;
    2|2
    1|3
    2|5
    1|6000
    

A having clause can contain the same filtering as a where clause, but can also include calls to aggregate functions. For the fastest execution and clearest use of the language, a condition that filters individual rows based on their contents should appear in a where clause, while a having clause should be used only when aggregation is required in the condition (such as specifying a minimum count for a group).

When using a group by clause, column descriptions can contain expressions that do not aggregate. In some cases, the SQL interpreter will choose the value from a row that corresponds to another column that includes aggregation. For example, the following statement gives the name of an animal with maximal weight.

sqlite> select name, max(weight) from animals;
    t-rex|12000
    sqlite> select name, legs, max(weight) from animals group by legs;
    t-rex|2|12000
    dog|4|20
    

However, whenever the row that corresponds to aggregation is unclear (for instance, when aggregating with count instead of max), the value chosen may be arbitrary. For the clearest and most predictable use of the language, a select statement that includes a group by clause should include at least one aggregate column and only include non-aggregate columns if their contents is predictable from the aggregation.