0

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 )

Cœur
  • 37,241
  • 25
  • 195
  • 267
ahesasa
  • 3
  • 1
  • You have the method how to accomplish the expected output. Where exactly did you get stuck in implementing it? Also, you should be a bit more careful in how you formulate your questions because what you asked can be answered by a simple yes or no. – Shadow Oct 26 '16 at 09:50
  • Moreover, I do not understand why you want to unpivot the above query's result, when you can get the row based output with a simple group by and count. – Shadow Oct 26 '16 at 09:54

1 Answers1

0

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
P.Salmon
  • 17,104
  • 2
  • 12
  • 19