Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this at front end:
count(if(aggr(count(distinct Type),ID)>=2,ID))
Thanks and regards,
Arthur Fong
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;
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
Try this at front end:
count(if(aggr(count(distinct Type),ID)>=2,ID))
Thanks and regards,
Arthur Fong