9 Replies Latest reply: Nov 21, 2012 12:36 PM by Celambarasan Adhimulam RSS

    Count of IDs with criteria

      I have a table wich includes the following columns as well as others

       

      ID | Agentkey | trans_num |

       

      There are many cases where for one ID there may be multiple rows

       

      ID | Agentkey | trans_num |

      12| 44455544 | 953213213 |

      12| 44455521 | 953213213 |

      12| 44455522 | 953213213 |

      12| 44455540 | 953213213 |

      13| 44455544 | 953213213 |

      13| 44455503 | 953213213 |

      13| 44455512 | 953213213 |

      15| 44455544 | 953213213 |

       

      I want to design a filter based on the count of agent keys per ID in this case the available filters would be 4,3 and 1.

      4 agent keys associated with ID 12,

      3 with ID 13 and

      1 with ID 15

       

      What would be the best way to accomplish this?

       

      I thought Count(Total<ID> Agentkey) would work but it doesn't

       

      Any help would be appreciated.

      Rob