使用SQL查询获取特定类别和进一步分支的子类别的计数 [英] Get count of particular category and further bifurcated sub category using SQL query

问题描述

我希望使用sql查询从数据库中获取摘要报告，以显示按类别对它们进行分组的计数，还可以进一步显示每个类别下的分组子类别计数. 例如在下面的快照中:我想总结一下我的男性和女性雇员数量数据，还要显示每种性别下不同雇员职位的数量.预先感谢.

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.

推荐答案

假定表结构如下:

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

您可以UNION ALL一系列聚合查询以产生预期的结果.

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

SELECT cat, cnt 
FROM (
    SELECT 1 rn, 'Male' cat, SUM(gender = 'Male') cnt FROM mytable
    UNION ALL SELECT 2, 'Senior Manager', SUM(gender = 'Male' and position = 'Senior Manager')   FROM mytable
    UNION ALL SELECT 3, 'Manager',        SUM(gender = 'Male' and position = 'Manager')          FROM mytable
    UNION ALL SELECT 4, 'Employee',       SUM(gender = 'Male' and position = 'Employee')         FROM mytable
    UNION ALL SELECT 5, 'Female',         SUM(gender = 'Female')                                 FROM mytable
    UNION ALL SELECT 6, 'Senior Manager', SUM(gender = 'Female' and position = 'Senior Manager') FROM mytable
    UNION ALL SELECT 7, 'Manager',        SUM(gender = 'Female' and position = 'Manager')        FROM mytable
    UNION ALL SELECT 8, 'Employee',       SUM(gender = 'Female' and position = 'Employee')       FROM mytable
)
ORDER BY rn

附加列rn用于在结果集中按顺序保留记录.

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

这篇关于使用SQL查询获取特定类别和进一步分支的子类别的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！