Oracle Get sum of distinct group without subquery

Question

I already have a working example which does exactly what I need. Now the problem is, that I'm not really a fan of subqueries and I think there could be a better solution to this problem.

So here is my (already) working example:

with t as
(
select  'Group1' as maingroup,'Name 1' as subgroup, 'random' as random, 500 as subgroupbudget from dual
union all
select 'Group1','Name 1','random2',500 from dual
union all
select 'Group1','Name 2','random3', 500 from dual
union all
select 'Group2','Name 3','random4', 500 from dual
union all
select 'Group2','Name 4','random5',500 from dual
union all
select 'Group2','Name 5', 'random6',500 from dual
)
select
maingroup,
subgroup,
random,
(select distinct sum(subgroupbudget) over(partition by maingroup) from t b where a.maingroup=b.maingroup group by maingroup,subgroup,subgroupbudget) groupbudget
from t a
group by  maingroup, subgroup ,subgroupbudget, random
order by maingroup, subgroup

As you can see, the with-clause shows a simplified table with data. Now the problem is that the last column is the budget of the subgroup. In the result I need the budget of the maingroup. That means I have to sum all values within the maingroup, but only if the subgroups are different (Here I need some kind of distinct).

Unfortunately a simple

sum(distinct subgroupbudget) over(partition by maingroup)

won't work because the numbers (subgroupbudget) can be the same (like in the example)

Answer 1

Assuming that for a maingroup/subgroup, the subgroupbudget is always the same (or you only take the highest value for the subgroup), this should work:

with t as (select  'Group1' as maingroup,'Name 1' as subgroup, 'random' as random, 500 as subgroupbudget from dual
           union all
           select 'Group1','Name 1','random2',500 from dual
           union all
           select 'Group1','Name 2','random3', 500 from dual
           union all
           select 'Group2','Name 3','random4', 500 from dual
           union all
           select 'Group2','Name 4','random5',500 from dual
           union all
           select 'Group2','Name 5', 'random6',500 from dual),
    t1 as (select maingroup,
                  subgroup,
                  random,
                  case when row_number() over (partition by maingroup, subgroup order by subgroupbudget desc) = 1 then subgroupbudget
                  end subgroupbudget
           from t)
select maingroup,
       subgroup,
       random,
       sum(subgroupbudget) over (partition by maingroup) groupbudget
from   t1;

MAINGROUP SUBGROUP RANDOM  GROUPBUDGET
--------- -------- ------- -----------
Group1    Name 1   random         1000
Group1    Name 1   random2        1000
Group1    Name 2   random3        1000
Group2    Name 3   random4        1500
Group2    Name 4   random5        1500
Group2    Name 5   random6        1500

It is effectively saying that for a maingroup/subgroup, you only want to use one of the values (the highest) of the rows in that subgroup in the sum.

Whether it's "better" (i.e. more performant) than your original query is something that you would have to test. Sub-queries are not necessarily a bad thing; they are a tool, and sometimes they're the right tool to use.