Oracle Analytics Get Summary Counts-CodePudding

Given this query, how can I get summary counts for each Tree_ID?

with tree_row
as (
select '1111' as tree_id, 'PULP' as tree_product from dual union all
select '1111' as tree_id, 'PULP' as tree_product from dual union all
select '2222' as tree_id, 'PULP' as tree_product from dual union all
select '2222' as tree_id, 'CHIP' as tree_product from dual union all
select '3333' as tree_id, 'PULP' as tree_product from dual union all
select '3333' as tree_id, 'CHIP' as tree_product from dual union all
select '3333' as tree_id, 'CHIP' as tree_product from dual
)
select distinct tree_id, 
       count(*) over (partition by tree_id, tree_product) as pulp_count,
       count(*) over (partition by tree_id, tree_product) as chip_count,
       count(*) over (partition by tree_id)               as tree_total
       from tree_row;

Desired Result

TREE_ID PULP_COUNT  CHIP_COUNT TREE_TOTAL
    1111     2           0            2
    2222     1           1            2
    3333     1           2            3

CodePudding user response：

You don't need analytic functions, you can use conditional aggregation - normal aggregation but with a case expression (the conditional part) to determine which rows to include in the count:

with tree_row
as (
select '1111' as tree_id, 'PULP' as tree_product from dual union all
select '1111' as tree_id, 'PULP' as tree_product from dual union all
select '2222' as tree_id, 'PULP' as tree_product from dual union all
select '2222' as tree_id, 'CHIP' as tree_product from dual union all
select '3333' as tree_id, 'PULP' as tree_product from dual union all
select '3333' as tree_id, 'CHIP' as tree_product from dual union all
select '3333' as tree_id, 'CHIP' as tree_product from dual
)
select tree_id, 
       count(case when tree_product = 'PULP' then tree_id end) as pulp_count,
       count(case when tree_product = 'CHIP' then tree_id end) as chip_count,
       count(*)                                                as tree_total
       from tree_row
       group by tree_id;

TREE_ID	PULP_COUNT	CHIP_COUNT	TREE_TOTAL
1111	2	0	2
2222	1	1	2
3333	1	2	3

fiddle