mysql - 截断表后 PostgreSQL 错误 22012 除以零

Question

因此，在我截断表后，这个以前有效的查询现在不起作用了。即使我已经再次填满了我的 table ，它仍然不起作用。这是我的 postgreSQL 代码:

SELECT 
                     k.reviewer                                               AS namareviewer
                     , COUNT(k.formcode)                                        AS actual
                     , ROUND((0.2*COUNT(k.formcode)))                           AS target
                     , SUM(CASE WHEN k.blibliknowledge != '' THEN 1 ELSE 0 END) AS blibli
                     , COUNT(CASE WHEN k.solusi != '' THEN 'foo' ELSE NULL END) AS solusi
                     , ROUND(((COUNT(CASE WHEN k.solusi != '' THEN 'foo' ELSE NULL END)+ SUM(CASE WHEN k.blibliknowledge != '' THEN 1 ELSE 0 END)) /ROUND((0.2*COUNT(k.formcode))))*100,2) as Percentage
                  FROM kpi k

                 GROUP
                    BY k.reviewer

还有我的 KPI 表

该数据与查询已经运行的第一个数据相同。为什么在第一个表被截断后会出现错误 SQL state : 22012 devided by zero ？提前谢谢您:D

Answer 1

使用NULLIF避免被零除错误

....
, ROUND(((COUNT(CASE WHEN k.solusi != '' THEN 'foo' ELSE NULL END)+ SUM(CASE WHEN k.blibliknowledge != '' THEN 1 ELSE 0 END))
   /NULLIF(ROUND((0.2*COUNT(k.formcode))))*100,2) ,0) as Percentage
........

完整代码:

SELECT
  k.reviewer AS namareviewer,
  COUNT(k.formcode) AS actual,
  ROUND((0.2 * COUNT(k.formcode))) AS target,
  SUM(CASE
    WHEN k.blibliknowledge != '' THEN 1
    ELSE 0
  END) AS blibli,
  COUNT(CASE
    WHEN k.solusi != '' THEN 'foo'
    ELSE NULL
  END) AS solusi,
  ROUND(((COUNT(CASE
    WHEN k.solusi != '' THEN 'foo'
    ELSE NULL
  END) + SUM(
  CASE
    WHEN
      k.blibliknowledge != '' THEN 1
    ELSE 0
  END)) / NULLIF(ROUND((
  0.2 * COUNT(k.formcode))), 0)) * 100, 2) AS Percentage
FROM kpi k
GROUP BY k.reviewer