Get Count Of Particular Category And Further Bifurcated Sub Category Using Sql Query

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

Solution 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
GROUPBY 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
GROUPBY Gender, Role WITHROLLUP

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:

SELECTCOALESCE(Role, Gender) asDesc, Ctr
(
  SELECT Gender, Role, COUNT(*) as Ctr
  FROM employee
  GROUPBY Gender, Role WITHROLLUP
) x --need to use a subquery - see manualORDERBY 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
GROUPBY Gender, Role WITHROLLUP

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

Solution 2:

Assuming a table structure as follows:

id        -- primary key
gender    -- 'Male', 'Female'
position  -- 'Senior Manager', 'Manager', 'Employee'

You could UNION ALL a series of aggregated queries to produce the expected results.

SELECT cat, cnt 
FROM (
    SELECT1 rn, 'Male' cat, SUM(gender ='Male') cnt FROM mytable
    UNIONALLSELECT2, 'Senior Manager', SUM(gender ='Male'and position ='Senior Manager')   FROM mytable
    UNIONALLSELECT3, 'Manager',        SUM(gender ='Male'and position ='Manager')          FROM mytable
    UNIONALLSELECT4, 'Employee',       SUM(gender ='Male'and position ='Employee')         FROM mytable
    UNIONALLSELECT5, 'Female',         SUM(gender ='Female')                                 FROM mytable
    UNIONALLSELECT6, 'Senior Manager', SUM(gender ='Female'and position ='Senior Manager') FROM mytable
    UNIONALLSELECT7, 'Manager',        SUM(gender ='Female'and position ='Manager')        FROM mytable
    UNIONALLSELECT8, 'Employee',       SUM(gender ='Female'and position ='Employee')       FROM mytable
)
ORDERBY rn

Additional column rn is there to keep the records in order in the resultset.