I'm trying to figure out how to count all presidents, managers etc and then convert columns to rows in one query.
For example there is some sample db 'employee' here: http://www.mysqltutorial.org/tryit/
I can count employees of all types using query like this:
WHEN jobTitle = 'President' THEN 1
END) AS 'Presidents',
WHEN jobTitle LIKE 'VP%' THEN 1
END) AS 'VPs',
WHEN jobTitle LIKE '%Manager%' THEN 1
END) AS 'Managers',
WHEN jobTitle LIKE '%Rep' THEN 1
END) AS 'Reps'
You could use a sub query to standardise the job titles, then group by and order by with a case statement to to produce the output in descending order of bossiness.
select jobtitle,count(*) from ( SELECT case WHEN jobTitle = 'President' THEN 'Presidents' WHEN jobTitle LIKE 'VP%' THEN 'VPs' WHEN jobTitle LIKE '%Manager%' then 'Managers' WHEN jobTitle LIKE '%Rep' then 'Reps' end as Jobtitle FROM employees ) s group by Jobtitle order by case when jobtitle = 'Presidents' then 1 when jobtitle = 'VPs' then 2 when jobtitle = 'Managers' then 3 when jobtitle = 'Reps' then 4 end