A nonaggregate attribute is a selected column or expression that is not computed by an aggregate function such as COUNT or AVG. When a query uses GROUP BY, every nonaggregate attribute in SELECT should also appear in GROUP BY. section 1.8
Grouping products by manufacturer
This query is consistent because manufacturer is both selected and grouped: SELECT AVG(price) AS avg_price, manufacturer FROM product GROUP BY manufacturer;. Adding product_id or name without grouping it can produce an error or a misleading result. section 1.8
Use this practical check:
- Find each selected column or expression that is not inside an aggregate function. section 1.8
- Confirm that every such attribute appears in GROUP BY; otherwise, expect an error or unreliable output. section 1.8
-- comment: will either lead to an error -- comment: or yield a misleading result SELECT product_id, AVG(price) AS avg_price, manufacturer FROM product GROUP BY manufacturer;
Inspect a grouped AVG query that selects product_id without grouping it, then compare the reported error. section 1.8
