无法在ORACLE 11G中显示列总数11G

Question

执行此查询时，我得到GRAND TOTAL列空白（ORACLE 11G）。

QUERY：

 
select NVL(TO_CHAR(APPROVER_EMPLOYEE_NAME), 'Grand Total') as EMPLOYEE_NAME, 
SUM(ONE_DAY) one_day, 
SUM(TWO_DAYS) TWO_DAYS , 
SUM(THREE_DAYS) THREE_DAYS, 
SUM(ONE_DAY + TWO_DAYS + THREE_DAYS) TOTAL 
from ic_request 
group by rollup (APPROVER_EMPLOYEE_NAME); 

EMPLOYEE_NAME   ONE_DAY  TWO_DAYS  THREE_DAYS  TOTAL 
-------------------------------------------------------------------------- 
abc      271   64  
xyz      60   09    01  
-------------------------------------------------------------------------- 
Grand Total    331   73    01 

存在对应于 'ABC' 行和 'THREE_DAYS' 列中没有值。 任何人都可以请帮助显示“合计”栏，其中总结了行总计>

Answer 1

这是因为该行abcTHREE_DAYS栏为空。您可以使用COALESCE将零归零并正确添加。

查询

select NVL(TO_CHAR(APPROVER_EMPLOYEE_NAME), 'Grand Total') as EMPLOYEE_NAME, 
SUM(COALESCE(ONE_DAY, 0)) one_day, 
SUM(COALESCE(TWO_DAYS, 0)) TWO_DAYS , 
SUM(COALESCE(THREE_DAYS, 0)) THREE_DAYS, 
SUM(COALESCE(ONE_DAY, 0) + COALESCE(TWO_DAYS, 0) + COALESCE(THREE_DAYS, 0)) TOTAL 
from ic_request 
group by rollup (APPROVER_EMPLOYEE_NAME);