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.
One system that handles this nonconforming ...