Help with set analysis

[saadaouianouar]

Hello guys,

I need your help! I have this SQL query and I need to reform it in set analysis! 
this is the SQL query : 

select IDOSS,count(IDOSS) from
(select distinct IDOSS , IDUnitOrg from UniOrg )
group by IDOSS
Having count(IDOSS) > 1
order by count(IDOSS)

this is what I tried to do : 

sum (Aggr (Count(
distinct [__Organizational_Unit_Code] & [__FOLDER_ID]) ,__FOLDER_ID,__Organizational_Unit_Code))

but It shows wrong results because I couldn't translate "Having count(IDOSS) > 1" 

Thank you for your help !  I really appreciate it 

[Anil_Babu_Samineni]

This is sufficient

Sum(Aggr(count({<IDOSS = {"=Count(IDOSS)>1"}>} IDOSS), IDOSS))

Or

If you have QV 12 above use

Sum(Aggr(count({<IDOSS = {"=Count(IDOSS)>1"}>} IDOSS), (IDOSS,(=Count({1}  IDOSS),Desc))))

[saadaouianouar]

Thank you for your help but unfortunally it does not show the wished results! I think we need to include distinct because I'm counting the occurence of IDOSS compared to the IDUnitOrg when count(IDOSS) > 1 .

the set analysis I wrote it shows the results that are > 1 and also the ones that are equal 1.

Thank you for your time 🙂 

[sunny_talwar]

Try this may be

Sum({<UniOrg = {"=Count(DISTINCT IDOSS) > 1"}>}Aggr(Count(DISTINCT IDOSS), IDOSS, UniOrg))

or may be give this a shot as well

Count(DISTINCT{<UniOrg = {"=Count(DISTINCT IDOSS) > 1"}>} IDOSS)

[saadaouianouar]

ty for replie but the output of the request must be   

 
and your request output :

[sunny_talwar · Accepted solution]

Can you try this

Count(DISTINCT{<IDOSS = {"=Count(DISTINCT UniOrg) > 1"}>} IDOSS)

[saadaouianouar]

Thank you very much for your help but can you please explain it more to me  🙂

[sunny_talwar]

Not sure what is there to explain... all I am doing is DISTINCT counting IDOSS where Count(DISTINCT UniOrg) is more than 1. So, if for a single IDOSS Count(DISTINCT UniOrg) = 1, then it isn't counted.

Does that help?

[saadaouianouar]

Thank you very much Sunny  You made my day  🙂

[sunny_talwar]

Awesome 🙂 I am glad I did