-- Show all the groups with members, and how many members in each group
-- Duplicates cannot be counted, because they cannot exist in the database
SELECT 
     groups.artist_id,
     groups.name, 
     count(persons.artist_id) AS members
FROM persons NATURAL JOIN persons_groups NATURAL JOIN groups 
GROUP BY groups.artist_id;

-- Show all groups, and number of members
SELECT 
     g.artist_id,
     g.name,
     count(pg.person_id) AS members
FROM groups g LEFT OUTER JOIN persons_groups pg ON (g.artist_id = pg.group_id)
group by g.artist_id;

-- Shows the number of certain roles for a particular group
SELECT role, count(*) 
FROM persons p NATURAL JOIN persons_groups pg
WHERE p.artist_id = pg.person_id AND p.artist_id = 1
GROUP BY ROLE;

-- Show a list of types and how many collections within
SELECT collections.type, count(collections.name)
FROM collections
GROUP BY collections.type;

-- Show a list of types and how many collections within, with a WHERE condition
SELECT c.type, count(c.name)
FROM works_collections NATURAL JOIN collections AS c
WHERE work_id > 0 # or whatever
GROUP BY c.type;

-- Show a list of collections and how many works within
SELECT collections.name, count(*)
FROM works_collections NATURAL JOIN collections
WHERE work_id > 0 # or whatever
GROUP BY collections.name;

-- Show a list of occasions and how many works within
SELECT occasion, count(*)
FROM works NATURAL JOIN occasions 
WHERE work_id > 0 # or whatever
GROUP BY occasion;