Count ID in a term if they don't exist in a previous term 2

[kernal]

Quick sample of table

Person
ID Term
1 1078
1 1082

2 1026
2 1088

3 1078

4 1122
4 1138

I want to count an ID if their first term ends in 8 and don't count them if their first term doesn't end in 8 so results:

Term Count ID
1078 2 (ID 1 and 3)
1088 0 (since ID 2 has their first term ending with 6 they are not included)
1138 0 (since ID 4 has their first term ending with 2 they are not included)

Help is appreciated. Thanks

 

[carp]

Try this:

SELECT term, count(*)
  FROM my_table
 WHERE substr(term,-1,1) = '8'
GROUP BY term;

 

[Beilstwh]

select term,sum(count_id) count_id
from
(
select term,count(*) count_id
from
(
select id,term
from
(
select id,term,
dense_rank() over (partition by id order by id,term) rn
from whb_test)
where rn = 1
and term like '%8')
group by term
union all
select distinct term,0
from whb_test)
group by term
order by term


Bill
Lead Application Developer
New York State, USA

 

[kernal]

Thanks everyone.