How should I write query to return a count of 3 for Farms Beginning with K?
Why does (partition by id,substr(farm,1))
calculate to 1
with tree_harvest
as (
select 1 as id, 'PINE' as tree, 'K001' as farm from dual union all
select 1 as id, 'PINE' as tree, '0003' as farm from dual union all
select 1 as id, 'PINE' as tree, 'K002' as farm from dual union all
select 1 as id, 'PINE' as tree, 'K003' as farm from dual
)
select id, tree,farm,
count(*) over (partition by id) as id_count,
case
when regexp_like(farm,'^K','i')
then count(*) over (partition by id,substr(farm,1))
else 0
end as k_count
from tree_harvest;
Desired Result
ID TREE FARM ID_COUNT K_COUNT
1 PINE 0003 4 0
1 PINE K001 4 3
1 PINE K002 4 3
1 PINE K003 4 3
CodePudding user response:
Here is a solution that fixes your issue, and should be faster (more efficient) than your current approach. Note that here both analytic functions partition by id
only; the conditional count is taken care separately, within the count()
call itself. Also both comparisons to K or k are case insensitive; in your attempted query one of the comparisons wasn't. I also avoid regular expressions (slower), which are not needed here.
with tree_harvest
as (
select 1 as id, 'PINE' as tree, 'K001' as farm from dual union all
select 1 as id, 'PINE' as tree, '0003' as farm from dual union all
select 1 as id, 'PINE' as tree, 'K002' as farm from dual union all
select 1 as id, 'PINE' as tree, 'K003' as farm from dual
)
select id, tree,farm,
count(*) over (partition by id) as id_count,
case when lower(farm) like 'k%' then
count(case when lower(farm) like 'k%' then 1 end)
over (partition by id) else 0 end as k_count
from tree_harvest;
ID TREE FARM ID_COUNT K_COUNT
---------- ---- ---- ---------- ----------
1 PINE K001 4 3
1 PINE K003 4 3
1 PINE K002 4 3
1 PINE 0003 4 0