Subqueries can be used in the column list. To get a count of related records from another table, use a correlated subquery. Here's an example that shows a list of base tables in the current file along with a count of fields in each table. Note, the alias from the outer query must be referenced in the WHERE clause of the inner query to make the magic happen.
SELECT DISTINCT basetablename,
FROM filemaker_fields ff
WHERE ff.tablename = ft.tablename)
FROM filemaker_tables ft
Is there another way to return additional fields when doing a GROUP BY without writing a lot of convoluted code?