MySQL converting columns to rows

Question

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:

SELECT 
    SUM(CASE
        WHEN jobTitle = 'President' THEN 1
        ELSE 0
    END) AS 'Presidents',
    SUM(CASE
        WHEN jobTitle LIKE 'VP%' THEN 1
        ELSE 0
    END) AS 'VPs',
    SUM(CASE
        WHEN jobTitle LIKE '%Manager%' THEN 1
        ELSE 0
    END) AS 'Managers',
    SUM(CASE
       WHEN jobTitle LIKE '%Rep' THEN 1
       ELSE 0
    END) AS 'Reps'
FROM
employees;

But now I want to convert columns to rows and I have no idea how to include it in a query similar to the answer here: Mysql Convert Column to row (Pivot table )

Answer 1

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