mysql - 计算列中的不同值，同时按不同的列值进行总计和分组

Question

我们有一个表，其中包含来自不同节点的数据，其中一列的状态报告为“合规或不合规”，示例数据如下

我想以这样的方式过滤表:如果节点上的任何检查显示不合规，则应将其标记为不合规，其余为合规。使用下面的查询我能够做到这一点

SELECT COUNT(*) AS total_nodes, 
       SUM(fully_compliant = 0) AS Non_compliant_nodes, 
       SUM(fully_compliant = 1) AS compliant_nodes 
FROM ( 
    SELECT Node, CASE WHEN SUM(Status = 'Compliant') = COUNT(*) THEN 1 ELSE 0 END AS fully_compliant 
    FROM your_table GROUP BY Node 
)

现在，我想按部门对结果进行分组和拆分，如下所示，如何实现此目的

Answer 1

我认为您正在寻找这个:

select    dept,
          count(*) as total_nodes,
          sum(case when non_compliant_chk = 0 then 1 else 0 end) as compliant_nodes,
          sum(case when non_compliant_chk > 0 then 1 else 0 end) as non_compliant_nodes
from      (
            select    dept,
                      node,
                      sum(case when 'Non-Compliant' then 1 else 0 end) as non_compliant_chk
            from      your_table
            group by  dept,
                      node
          ) v
group by  dept;