复杂sql语句：按部门统计人数

复杂的sql语句，按部门统计人数：

--按部门统计人数

SELECT o.OUGUID AS OUGUID, o.OUNAME AS OUNAME,

IFNULL(COUNT(DISTINCT o.USERGUID), 0) AS USERNUM,

SUM(CASE WHEN IFNULL(q.CALLTIME, 0) != 0 THEN 1 ELSE 0 END) AS CallNoCount,

SUM(CASE WHEN q.ROW_ID = '' THEN 1 ELSE 0 END) AS QuestionCount,

SUM(CASE WHEN q.`STATUS` = '' THEN 1 ELSE 0 END) AS PassCount,

SUM(CASE WHEN q.`STATUS` = '' OR q.`STATUS` = '' THEN 1 ELSE 0 END) AS BanjianCount

FROM

    (SELECT ROW_ID,`STATUS`,handleuserguid,CALLTIME FROM audit_queue UNION ALL

        SELECT ROW_ID,`STATUS`,handleuserguid,CALLTIME FROM audit_queue_history) q

LEFT JOIN (SELECT o.OUNAME,o.OUGUID,u.USERGUID, u.DISPLAYNAME FROM frame_user u

                        LEFT JOIN frame_ou o ON u.OUGUID = o.OUGUID) o

    ON q.handleuserguid = o.USERGUID

WHERE q.CALLTIME BETWEEN '2018-09-30 00:00:00' AND '2018-10-30 23:59:59'

GROUP BY o.OUGUID;

其中类似 SUM(CASE WHEN q.ROW_ID = '1' THEN 1 ELSE 0 END) AS QuestionCount 为按一定的条件计数；