SQL -尝试根据大小写计算记录(简单？)

Question

非常简单的情况-我无法正确计算下表的月数-预期的数字是2，而不是3-有趣的是，在同样的条件下，"valueagg“被正确计算出来了！

INSERT INTO ForgeRock
    ([productName], [description], [month], [value] )
VALUES
    ('OpenIDM', 'Y',1,33.2),
    ('OpenIDM', 'Y',2, 12.1),
    ('OpenIDM', 'Y',3, 1.1)
;

测试

SELECT
  productName,
  description,
    sum(CASE WHEN [MONTH] between 1 and 2 then [VALUE] else 0 end) valueagg    --works
 ,count(CASE WHEN [MONTH] between 1 and 2 then [MONTH] else 0 end) month_count --doesn't work
 ,count(CASE WHEN [MONTH] between 1 and 2 then 1 else 0 end) month_count2      --doesn't work
FROM
  ForgeRock
  group by   productName,
  description

结果

| productName | description |           valueagg | month_count | month_count2 |
|-------------|-------------|--------------------|-------------|--------------|
|     OpenIDM |           Y | 45.300000000000004 |           3 |            3 |

http://sqlfiddle.com/#!18/14603/3/0

Answer 1

您正在将count()与else一起使用。count()计算非空值的数量，并且-- 0和任何其他值一样都是"not NULL“。

请改用sum()：

SELECT productName, description,
       SUM(CASE WHEN [MONTH] between 1 and 2 then [VALUE] else 0 end) as valueagg    --works
       SUM(CASE WHEN [MONTH] between 1 and 2 then [MONTH] else 0 end) as month_count --doesn't work
       SUM(CASE WHEN [MONTH] between 1 and 2 then 1 else 0 end) month_count2      --doesn't work
FROM ForgeRock
GROUP BY productName, description

或者从COUNT()中删除ELSE。

Answer 2

删除CASE表达式的ELSE部分：

SELECT
  productName,
  description,
    sum(CASE WHEN [MONTH] between 1 and 2 then [VALUE] else 0 end) valueagg
    ,count(CASE WHEN [MONTH] between 1 and 2 then [MONTH] end) month_count
FROM
  ForgeRock
  group by   productName,
  description

或者替换为SUM()，如下所示：

SELECT
  productName,
  description,
    sum(CASE WHEN [MONTH] between 1 and 2 then [VALUE] else 0 end) valueagg
    ,sum(CASE WHEN [MONTH] between 1 and 2 then 1 else 0 end) month_count
FROM
  ForgeRock
  group by   productName,
  description