Count where a value in a field has multiple values in another field

[JohnShanahan]

I'm trying to figure out a way to count how many ids in a table have more than one instance of another field.  From the table below the Product doesn't matter I'm trying to count how many IDs have more than one Type.  The count for below should be 2.

Product	ID	Type
ProductA	ID1	SCHEDULED
ProductB	ID1	ADHOC
ProductB	ID2	SCHEDULED
ProductC	ID2	SCHEDULED
ProductA	ID2	SCHEDULED
ProductA	ID3	SCHEDULED
ProductB	ID3	ADHOC
ProductD	ID3	SCHEDULED
ProductA	ID4	ADHOC

Thanks

[miskinmaz]

At the back end try to use below code:

tmp:

Load distinct 

ID,

count(Type) as total_count

from Table1

group by ID;

Load 

ID

resident tmp where total_count>1;

drop table tmp;

[Brett_Bleess]

John, did Miskin's post help you get what you needed or are you still working on things?  If it did work, be sure to come back to the thread and use the Accept as Solution button on that post to give credit for the help and to let others know that was a good solution.  If you still need help, leave an update.

Regards,
Brett

[Arthur_Fong · Accepted solution]

Try this at front end:

count(if(aggr(count(distinct Type),ID)>=2,ID))

Thanks and regards,

Arthur Fong