Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Cautions and Restrictions

GROUP BY looks straightforward, but it's caused its share of headaches for SQL users. Here, for example, is a query that seems reasonable but won't work on most systems:

SQL (variant)

select pub_id, type, count(title)
from titles
group by pub_id
					

Since the table is divided into sets by publisher (group by pub_id) and there are three publishers, the query must return no more than three rows, and each SELECT list item must have a single value for the set. Unfortunately, there is more than one type per publisher, so most systems find the query impossible to answer. You could solve this problem by adding type to the GROUP BY clause, as shown in Groups Within Groups.

SQL VARIANTS One system that handles this nonconforming ...