ahesasa ahesasa - 1 month ago 12
MySQL Question

MySQL converting colums to rows

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 )
Do you have any idea?

Answer

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