select name, id, sum (case when type = 'actor' then 1 else 0 end) as acts, sum (case when type = 'director' then 1 else 0 end) as directs, sum (case when type = 'writer' then 1 else 0 end) as writes, sum (case when type = 'producer' then 1 else 0 end) as produces, sum (case when type = 'editor' then 1 else 0 end) as edits, sum (case when type = 'composer' then 1 else 0 end) as composes, sum (case when type = 'cinematographer' then 1 else 0 end) as films, sum (case when type = 'costume_designer' then 1 else 0 end) as cdesigns, count(*) as total from people join credits on credits.person_id = id group by name, id order by total desc, name, id ;