Column with Partition/Group Row Count?
763900Sep 2 2010 — edited Sep 2 2010Hello All,
I would like to have a column in my query that says how many rows are in each partition/group. I hope my code is clear. Would anybody kow how I can do this? I'm quite keen to avoid nesting if I can as this is already part of a larger more complex query.
Be grateful for any help I'm a bit stumped!
Thank you
Jon
select
d.deal_id,
c.class_id,
c.seniority,
ROW_NUMBER( ) OVER ( PARTITION BY d.deal_id ORDER BY c.seniority ASC, c.class_id asc ) DealClassCount,
-------- I want the number of Classes per Deal here, max DealClassCount (the value would be the same on every row in the Deal) -----------
decode ( ROW_NUMBER( ) OVER ( PARTITION BY d.deal_id ORDER BY c.seniority ASC, c.class_id asc ),1,1,0) DealSum
from global_sf.deal d, global_sf.class c
where d.deal_id = c.deal_id
and d.deal_id in
(
'87354694'
,'87355187'
)
order by d.deal_id ,c.class_id ,c.seniority