I believe use cases for aggregate functions are clear from these functions.
- average
- sum
- count
- creation of string with list of values, array of values,
- min
- max
- boolean and/ or over some interval of values
- binary and/ or over some interval of values
- and of course all “usual” statistical functions like correlation, covariance etc…
If it makes sense for specific aggregate function we can use with this function in column computation:
- ORDER BY
- DISTINCT/ ALL
Interval of values over which functions calculate results is set by GROUP BY or GROUP BY HAVING clause.
Distinct individual values we can see in result only in columns over which we group results.
All other rows in result set must contain some aggregates.