Aggregate Functions (SQL)

GROUP BY

Aggregate functions are used in conjunction with the group by clause. A group by clause splits a resultset into groups of rows and aggregate functions perform calculations on them. (Ferrari and Pirozzi 2023, 131)

Function Description
AVG()  
COUNT()  
MAX()  
MIN()  
SUM()  
ARRAY_AGG  
JSON_AGG or JSONB_AGG  
BOOL_AND(Predicate)  
BOOL_OR(Predicate)   
  SELECT category, COUNT(*)
  FROM posts
  GROUP BY category;
  -- Outputs:
   category | count
  ----------+-------
          3 | 1
          1 | 2

You can also filter the result of a GROUP BY by using HAVING: 

  SELECT category, COUNT(*)
  FROM posts
  GROUP BY category
  HAVING COUNT(*) > 1;

and also pack multiple aggregations in a single SELECT statement: 

  SELECT
    category,
    COUNT(*),
    COUNT(*) FILTER (WHERE replies > 2) as number_of_replies
  FROM posts
  GROUP BY category
  HAVING COUNT(*) > 1;

GROUPING SETS, CUBE, and ROLLUP

More complex grouping operations than those described above are possible using the concept of grouping sets. The data selected by the FROM and WHERE clauses is grouped separately by each specified grouping set, aggregates computed for each group just as for simple GROUP BY clauses, and then the results returned.

References:

Ferrari, Luca, and Enrico Pirozzi. 2023. Learn Postgresql: Use, Manage, and Build Secure and Scalable Databases with Postgresql 16. 2nd ed. Packt Publishing Ltd.