Aggr inside aggr set analyis

[martapardo]

I have a table which is made by a list of users (aparcamiento_Usuario) which are doing some courses (Curso) with its state of the courses (aparcamiento_INICIADO) and with his responsable of the course (APARCAMIENTO).

APARCAMIENTO	aparcamiento_Usuario	Curso	aparcamiento_INICIADO
R1	U1	C1	0
R1	U1	C2	0
R1	U1	C3	0
R1	U2	C1	0
R1	U2	C2	1
R1	U3	C1	0
R1	U3	C2	0
R1	U3	C3	0

so i would like to know how many users has all curses with aparcamiento_INICIADO =0,(so the sum of aparcamiento_INICIADO for each users should be equal to 0), for each responsable (APARCAMIENTO)

In SQL I would do like this:

select APARCAMIENTO, count(1) count

from (select aparcamiento_USUARIO, sum(aparcamiento_INICIADO) sum_aparcamiento_INICIADO

               from  table

              group by aparcamiento_USUARIO)

where sum_aparcamiento_INICIADO = 0

group by APARCAMIENTO;

so the count is the number that I would like to have as ameasure, I have tried to do it in set analysis and my guess was:

Aggr(Count( distinct { $< Aggr( aparcamiento_Usuario ,(sum(aparcamiento_INICIADO), (aparcamiento_Usuario,))) = {0} > } aparcamiento_Usuario, APARCAMIENTO))

which is not working.

[Anil_Babu_Samineni]

Perhaps this?

Count(DISTINCT Aggr(sum({<aparcamiento_USUARIO = {"=Sum(aparcamiento_INICIADO)=0"}>} aparcamiento_INICIADO), aparcamiento_USUARIO))

[sunny_talwar]

May be this

Count(DISTINCT {<aparcamiento_Usuario = {"=Sum(aparcamiento_INICIADO) = 0"}>} aparcamiento_Usuario)

[martapardo]

Thanks, it works perfectly,
I have a herarchy, of Boss -> manager -> aparcamiento
BOSS MANAGER APARCAMIENTO Aparcamiento_usuario Curso aparcamiento_INICIADO
B1 M1 D1 U1 C1 0
B1 M1 D1 U1 C2 1
B1 M1 D1 U2 C1 0
B1 M1 D1 U2 C2 0
B1 M1 D2 U3 C1 0
B1 M1 D2 U3 C2 0
B1 M1 D2 U4 C1 0
B1 M2 D3 U5 C1 0
B1 M2 D3 U5 C2 0
B1 M2 D4 U6 C1 0
B1 M2 D4 U6 C2 0
B2 M3 D5 U7 C1 1
B2 M3 D5 U7 C2 0
B2 M3 D5 U7 C3 0
B2 M3 D6 U8 C1 0
B2 M3 D6 U8 C2 0
and I would like to do the same as before but doing the agroupation by level s the aggr that you told me is by APARCAMIENTO, and now I would like to do it going upper in the herarchy. Like;
select MANAGER, count(1) count

from (select aparcamiento_USUARIO, sum(aparcamiento_INICIADO) sum_aparcamiento_INICIADO

from table

group by aparcamiento_USUARIO)

where sum_aparcamiento_INICIADO = 0

group by MANAGER;

I thugh that it could be done with the same query as you told me but with a aggr as well:
Aggr(Count(DISTINCT {<aparcamiento_Usuario = {"=Sum(aparcamiento_INICIADO) = 0"}>} aparcamiento_Usuario), MANAGER)

[sunny_talwar]

Have you tried this

Count(DISTINCT {<aparcamiento_Usuario = {"=Sum(aparcamiento_INICIADO) = 0"}>} MANAGER)