Oracle - 计算 CASE 语句的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14216351/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle - counting the result of a CASE statement
提问by SeanKilleen
The Goal / Background
目标/背景
- I am combing through a workorder system to see if workorders meet certain criteria
- (e.g. if they're not stage 3, stage 2, or even stage 1).
- These "stages" have been defined by management.
- I would like to group by the year reported, then the craft, and then see how many work orders for that grouping are in each of those 3 "not stages".
- 我正在梳理工单系统以查看工单是否符合某些标准
- （例如，如果他们不是第 3 阶段、第 2 阶段甚至第 1 阶段）。
- 这些“阶段”已由管理层定义。
- 我想按报告的年份分组，然后按工艺分组，然后查看在这 3 个“非阶段”中的每一个中有多少该分组的工作订单。
The Query
查询
select yearreported
, theleadcraft
, count(NotStage3)
, count(NotStage2)
, count(NotStage1)
from
(
select extract(year from reportdate) as YearReported
, Nvl(leadcraft, 'NONE') as TheLeadCraft
, CASE when status not in ('CAN', 'CLOSE') then 1 else 0 END as NotStage3
, CASE when status not in ('CAN', 'CLOSE', 'COMP') then 1 else 0 END as NotStage2
, CASE when status not in ('CAN', 'CLOSE', 'COMP', 'WORKDONE') then 1 else 0 END as NotStage1
from workorder
) query
group by yearreported, theleadcraft;
;
The Problem / Question
问题/问题
- This appears to work, but all counts for notstage1, notstage2, and notstage1 come out the same, despite querying for some situations and finding some that I know to be different.
- Is this the correct way to implement a case statement that I want to count?
- Should I be using DECODE() instead?
- 这似乎有效，但对于 notstage1、notstage2 和 notstage1 的所有计数结果都是一样的，尽管查询了某些情况并发现了一些我知道不同的情况。
- 这是实现我想计算的 case 语句的正确方法吗？
- 我应该改用 DECODE() 吗？
Thanks in advance for any help!
在此先感谢您的帮助！
回答by David Aldridge
1's and 0's both COUNT() the same -- possibly you want to SUM(), or to COUNT() either 1 or null.
1 和 0 的 COUNT() 都是相同的——可能你想要 SUM()，或者 COUNT() 要么是 1 要么是 null。
回答by Hamlet Hakobyan
Count is not counting NULL -s. Try this:
Count 不计算 NULL -s。尝试这个：
, CASE when status not in ('CAN', 'CLOSE') then 1 END as NotStage3
, CASE when status not in ('CAN', 'CLOSE', 'COMP') then 1 END as NotStage2
, CASE when status not in ('CAN', 'CLOSE', 'COMP', 'WORKDONE') then 1 END as NotStage1
回答by Gordon Linoff
You should not be using decode.
你不应该使用decode.
The way you have your query written, you really want sum(), not count():
您编写查询的方式是您真正想要的sum()，而不是count()：
select yearreported, theleadcraft, sum(NotStage3), sum(NotStage2), sum(NotStage1)
The function count()has a misleading name when applied to a column (in my opinion). It is counting the number of non-NULL values. Since "1" and "0" are both non-NULL, they get counted.
该函数count()在应用于列时具有误导性名称（在我看来）。它正在计算非 NULL 值的数量。由于“1”和“0”都是非 NULL，因此它们被计算在内。
回答by Eng. Samer T
yes you can do it by simple modification on your statement above
是的，您可以通过简单修改上面的语句来实现
try this :
尝试这个 ：
select yearreported
, theleadcraft
, count(decode (NotStage3, 1,1) )
, count(decode (NotStage2, 1,1) )
, count(decode (NotStage1, 1,1) )
from
(
select extract(year from reportdate) as YearReported
, Nvl(leadcraft, 'NONE') as TheLeadCraft
, CASE when status not in ('CAN', 'CLOSE') then 1 else 0 END as NotStage3
, CASE when status not in ('CAN', 'CLOSE', 'COMP') then 1 else 0 END as NotStage2
, CASE when status not in ('CAN', 'CLOSE', 'COMP', 'WORKDONE') then 1 else 0 END as NotStage1
from workorder
) query
group by yearreported, theleadcraft;
regards,
问候，
