oracle sum()的定义,oracle sum（）与where子句

另一个特殊问题。我有以下数据和查询：

with helptab as (

select '6762' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '6762' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '6762' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '6762' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '6762' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '6762' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '6762' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '6763' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '6763' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '6763' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '6763' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '6763' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '6763' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '6763' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '6763' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '6763' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '6835' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '6835' as nr, 'B' as sub_val, 1 as sub_nr from dual

union all

select '6835' as nr, 'B' as sub_val, 1 as sub_nr from dual

union all

select '6835' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '6835' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'C' as sub_val, 1 as sub_nr from dual

union all

select '8904' as nr, 'C' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'C' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'C' as sub_val, 1 as sub_nr from dual

union all

select '8904' as nr, 'C' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8904' as nr, 'D' as sub_val, 0 as sub_nr from dual

union all

select '8905' as nr, 'B' as sub_val, 1 as sub_nr from dual

union all

select '8905' as nr, 'B' as sub_val, 0 as sub_nr from dual

union all

select '8905' as nr, 'C' as sub_val, 0 as sub_nr from dual

union all

select '8905' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8905' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8905' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8905' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8905' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8905' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8905' as nr, 'A' as sub_val, 0 as sub_nr from dual

union all

select '8905' as nr, 'D' as sub_val, 0 as sub_nr from dual)

select nr, sub_val, sub_nr, count(sub_val) as cnt

, case when sub_val = 'A' then 0 when sum(count(sub_val)) over(partition by nr) >= 5 then count(sub_val) else 0 end as MoreThan5

, case when sub_val = 'A' then count(sub_val) when sum(count(sub_val)) over(partition by nr) < 5 then count(sub_val) else 0 end as LessThan5

from helptab

group by nr, sub_val, sub_nr

order by nr, sub_val, sub_nr

应该如何工作的：

所有行上sub_val等于“A”必须有导致在LessThan5列。所有其他需要通过nr和Sub_val(除'A'之外)的总和进行检查。当小于5时，所有结果都必须放在LessThan5列上，否则放在MoreThan5上。我认为sum()over(Partition by)是正确的方式，但它不能正常工作。

2016-04-19

user

+4

两个简单的表中的问题 - 有样本数据和预期的结果 - 将真正帮助别人明白你想要做什么。 –

+0

我刚刚添加了一张照片(希望)使其更清晰 –

+0

请确保您阅读发布问题的指导原则。 http://stackoverflow.com/help/on-topic“寻求调试帮助的问题(”为什么不是这个代码工作？“)必须包含所需的行为，特定的问题或错误以及重现它所需的最短代码问题本身，没有明确问题陈述的问题对其他读者无益，请参阅：如何创建最小，完整和可验证示例。“ –