Aggregates doesn’t work on a complete result, they only work on a group in a result.
Consider a table containing:
Person Pet
-------- --------
Amy Cat
Amy Dog
Amy Canary
Dave Dog
Susan Snake
Susan Spider
If you use a query that groups on Person, it will divide the data into these groups:
Amy:
Amy Cat
Amy Dog
Amy Canary
Dave:
Dave Dog
Susan:
Susan Snake
Susan Spider
If you use an aggreage, for exmple the count aggregate, it will produce one result for each group:
Amy:
Amy Cat
Amy Dog
Amy Canary count(*) = 3
Dave:
Dave Dog count(*) = 1
Susan:
Susan Snake
Susan Spider count(*) = 2
So, the query select Person, count(*) from People group by Person
gives you one record for each group:
Amy 3
Dave 1
Susan 2
If you try to get the Pet field in the result also, that doesn’t work because there may be multiple values for that field in each group.
(Some databases, like MySQL, does allow that anyway, and just returns any random value from within the group, and it’s your responsibility to know if the result is sensible or not.)
If you use an aggregate, but doesn’t specify any grouping, the query will still be grouped, and the entire result is a single group. So the query select count(*) from Person
will create a single group containing all records, and the aggregate can count the records in that group. The result contains one row from each group, and as there is only one group, there will be one row in the result.