I'm new at this type of programs to do reports and have problems with some "large" sub-queries. I used in other report a sub-query with another table that only has 14 string values * 2 fields, so not a big deal at all. But this table, dw.equipament, have 113 rows and that makes the things slow. The main query is this:

select comarca as lloc, poblacio, categoria, num_equipaments,
poblacio/(select count(distinct(e.id_cat_equip)) from dw.equipament e, dw.oferta o,
dw.poblacio p where temps = ${temps} and (p.comarca = lloc and
o.id_poblacio = p.id_poblacio) and e.id_cat_equip = o.id_cat_equip) as sumatori
from dw.oferta o, dw.poblacio p, dw.equipament e where temps = ${temps} and
((p.provincia = ${zona} or p.comarca = ${zona}) and o.id_poblacio = p.id_poblacio) and
(${tipus}='Tots' and o.id_cat_equip = e.id_cat_equip)

union

select comarca as lloc, poblacio, categoria, num_equipaments,
poblacio/(select count(distinct(e.id_cat_equip)) from dw.equipament e, dw.oferta o,
dw.poblacio p where temps = ${temps} and (p.comarca = lloc and
o.id_poblacio = p.id_poblacio) and ((trim(e.grup) = trim(${tipus}) or
trim(e.categoria) = trim(${tipus})) and e.id_cat_equip = o.id_cat_equip)) as sumatori
from dw.oferta o, dw.poblacio p, dw.equipament e where temps = ${temps} and
((p.provincia = ${zona} or p.comarca = ${zona}) and o.id_poblacio = p.id_poblacio) and
(${tipus}!='Tots' and (trim(e.grup) = trim(${tipus}) or
trim(e.categoria) = trim(${tipus})) and o.id_cat_equip = e.id_cat_equip)

group by lloc, categoria
order by lloc, categoria

I calculate that new field, sumatori, as in the first report that combines two measures I say that doing Sum(Running) in the group footer was adding more than needed. For example, in a location with a population of 500K and 4 categories for "equipament" there was 4 rows that are same in the location, so was adding 2M to the population, and I want to sum only 500K for that one. With that field, sumatori, every one of that rows would have 500K / 4 categories = 125K. That variable was not going to appear in the report but used for the Sum(Running) later. My question is: is there a way to not have duplicate parts? I suppose that people will say "put a sub-query" but have tried to put a group inside the group and the result is the same, so don't know how to do it. My objective is transform what currently I have in this:

Name:  report.jpg
Views: 21
Size:  56.4 KB

As with 113 rows in equipment right now is insanely slow and want to forget that "sumatori" field. Thanks in advance.