如何计算sql中包含非零值的行 [英] How to count the rows which contains non zero values in sql

问题描述

SELECT round(COUNT(dmd_1wk),2) AS NBR_ITEMS_1WK
  FROM table;

字段 dmd_1wk 中有很多零.如何计算非零值?

Field dmd_1wk has so many zeros in it. How do I Count the non zero values?

推荐答案

Methinks bluefeet 的回答可能就是你真的在寻找，因为听起来你只想计算非零；但如果情况并非如此，这将为您提供零和非零项目的计数:

Methinks bluefeets answer is probably what you are really looking for, as it sounds like you just want to count non-zeros; but this will get you a count of zero and non-zero items if that's not the case:

SELECT 
  ROUND(SUM(CASE NVL(dmd_1wk, 0) = 0 THEN 1 ELSE 0 END), 2) AS "Zeros",
  ROUND(SUM(CASE NVL(dmd_1wk, 0) != 0 THEN 1 ELSE 0 END), 2) AS "NonZeros"
FROM table

虽然对整数进行四舍五入没有意义，但我已经包含了您的原始 ROUND，因为我猜您正在使用它进行格式化，但您可能想要使用:

Although there is no point in rounding a whole number, I've included your original ROUNDs as I'm guessing you're using it for formatting, but you might want to use:

TO_CHAR(SUM(...), '999.00')

因为这是格式化数字的预期功能.

as that's the intended function for formatting numbers.

这篇关于如何计算sql中包含非零值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！