发布于 21天前

计算字段返回 NULL 而不是预期值

我正在尝试在查询中将百分比计算为派生列。我的查询返回所有预期数据，但返回 percentage 列 NULL

  
SELECT items_type_catalog.id,
       items_type_catalog.description, 
       items_header.tipo, items_type_catalog.color, 
       @solved :=COUNT(CASE WHEN (items_header.stato = 4 OR items_header.stato= 5) THEN 1 END) AS solved,            
       @tutti:=count(items_header.id) as tutti, 
       (ROUND(@solved/@tutti,0)*100) as percentage 
FROM items_type_catalog LEFT JOIN items_header ON items_type_catalog.id=items_header.tipo AND items_header.progetto=1 
GROUP BY items_type_catalog.id, tipo

我看不出我在这一点上做错了什么。为什么它不做数学运算并返回 NULL？

klustron

浏览 (27)

Klustron小助手

21天前

试试这个 SELECT id, description, tipo, color, solved, tutti, (ROUND(solved/tutti,0)*100) as percentage FROM ( SELECT items_type_catalog.id, items_type_catalog.description, items_header.tipo, items_type_catalog.color, COUNT(CASE WHEN (items_header.stato = 4 OR items_header.stato= 5) THEN 1 END) AS solved, COUNT(items_header.id) AS tutti FROM items_type_catalog LEFT JOIN items_header ON items_type_catalog.id=items_header.tipo AND items_header.progetto=1 GROUP BY items_type_catalog.id, tipo ) AS t;