mysql - Get count of particular category and further bifurcated sub category using SQL query

Question

I am looking to get a summary report from my database using sql query to show their count grouped them by category but also to further show the grouped subcategory count under each category. e.g. in attached snapshot below: I want to summarize my data for number of male and female employees but also to show under each gender the count of different employee position. Thanks in advance.

标签： mysqlsqlgroup-by

Answer 1

MySQL group by supports WITH ROLLUP that will provide the broader aggregations for you:

Suppose your employee table is like:

Name, Role, Gender
John, Manager, Male
Susie, Manager, Female
...

A query like this:

SELECT Gender, Role, COUNT(*)
FROM employee
GROUP BY Gender, Role

Would produce a familiar:

Male, Manager, 5
Male, Senior Manager, 2
Male, Employee, 20

etc

Now, if we add WITH ROLLUP:

SELECT Gender, Role, COUNT(*)
FROM employee
GROUP BY Gender, Role WITH ROLLUP

Then MySQL will also ignore the Role and just group on Gender:

Male, Manager, 5
Male, Senior Manager, 2
Male, Employee, 20
Male, NULL, 29

The NULL role row is the row where all roles are lumped together and the count is the total of Males. ROLLUP rolls from right to left, so if you were to GROUP BY a,b,c,d WITH ROLLUP you'd get extra rows for "all a,b,c", "all a,b" and "all a" - so the order you put Gender and Role in your Group by is important!

Lastly, if you want to do a bit of data reshaping so you only have one column of text, like your example:

SELECT COALESCE(Role, Gender) as Desc, Ctr
(
  SELECT Gender, Role, COUNT(*) as Ctr
  FROM employee
  GROUP BY Gender, Role WITH ROLLUP
) x --need to use a subquery - see manual
ORDER BY Gender, Role

But do note that if you do this, you'll run into a problem because there isn't anything left to concretely tell the male "Manager" row apart from the female "Manager" row; it's purely reliant on the order, and that's not a great idea; it's why we typically leave subtotalling in this way to the front end, so the reports package will keep the data together. If you do something like convert this to JSON, send it to a remote computer and the ordering is lost, the info becomes meaningless. Personally I would do something more like:

SELECT Gender, COALESCE(Role, '(TOTAL)') as Role, COUNT(*)
FROM employee
GROUP BY Gender, Role WITH ROLLUP

It keeps the male-manager, and female-manager data on the row so you can tell them apart, but it converts the NULL to (Total) to better provide info on what it is

There are other things to discuss such as what if columns contain NULL values themselves, but i'll point you to The Fine Manual for that: https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html