PostgreSQL error code

PostgreSQL error 42803

42803 is the SQLSTATE PostgreSQL reports for grouping error, in class 42, Syntax Error or Access Rule Violation.

SQLSTATE	42803
Condition name	grouping_error
Class	42 — Syntax Error or Access Rule Violation

Reproduce it, then fix it

These are the queries from the guide below. Run the broken query to see the error, then switch to the fixed one. Edit either freely; every run is rolled back.

SELECT AVG(COUNT(*)) AS avg_products_per_category
FROM demo.products
GROUP BY category;

SELECT AVG(product_count) AS avg_products_per_category
FROM (
  SELECT category, COUNT(*) AS product_count
  FROM demo.products
  GROUP BY category
) per_category;

How to fix it

Explained step by step, with a query you can run

aggregate function calls cannot be nested

Explained step by step, with a query you can run

aggregate functions are not allowed in WHERE

Explained step by step, with a query you can run

column "x" must appear in the GROUP BY clause or be used in an aggregate function

In other databases

Source: PostgreSQL documentation, Appendix A