无法转到ORACLE 10G数据库

Question

我正试着用甲骨文10G转轴。Screen shot1是表中的实际数据。我正在尝试获取像屏幕快照2这样的数据。请建议我的枢轴功能不能在10G中工作。

。

​

Answer 1

你可以用conditional aggregation

SELECT ANALYST
    ,COUNT(CASE WHEN status = 'unchecked'   THEN 1 END) AS unchecked
    ,COUNT(CASE WHEN status = 'observation' THEN 1 END) AS observation
    ,COUNT(CASE WHEN status = 'supervisor'  THEN 1 END) AS supervisor
    ,COUNT(CASE WHEN status = 'pending'     THEN 1 END) AS pending
    ,COUNT(CASE WHEN status = 'closed'      THEN 1 END) AS closed
    ,COUNT(*) AS Grand_Tot
FROM tab
GROUP BY ANALYST
ORDER BY ANALYST;

编辑：

我能否知道如何将Grand_Tot列与我正在尝试的SUM(Grand_Tot)查询合并，但将错误作为无效标识符。

一种方法是使用子查询：

SELECT sub.*, SUM(Grand_Tot) OVER() AS Sum_Grant_Tot
FROM (
    SELECT ANALYST
        ,COUNT(CASE WHEN status = 'unchecked'   THEN 1 END) AS unchecked
        ,COUNT(CASE WHEN status = 'observation' THEN 1 END) AS observation
        ,COUNT(CASE WHEN status = 'supervisor'  THEN 1 END) AS supervisor
        ,COUNT(CASE WHEN status = 'pending'     THEN 1 END) AS pending
        ,COUNT(CASE WHEN status = 'closed'      THEN 1 END) AS closed
        ,COUNT(*) AS Grand_Tot
    FROM tab
    GROUP BY ANALYST
) sub
ORDER BY ANALYST;