Aggregate Query - Find Most Common??

[MattS]

I have a list of transactions processed on insurance policies, and am running an aggregate query to find the total amount on a policy for each client/underwriter. Each transaction has a created_by field, identifying who deals with it.

I can obviously get the aggregate query to give me the min or max of the created_by, but what I really want is the most common occurrence (i.e. 5 transactions with created_by id's of ABC, DEF, DEF, DEF, GHI - I want DEF to be returned).

Any ideas?

Thanks,

Matt.

 

[MattS]

I can't figure that out. As an example:

Policy Trans Company Amount Created_By

AB123456 A001 Client 1 1,000.00 MCS
AB123456 A001 Uwtr 1 -900.00 MCS
AB123456 A002 Client 1 2,500.00 FMD
AB123456 A002 Uwtr 1 -2,000.00 FMD
AB123456 A003 Client 1 -10,000.00 FMD
AB123456 A003 Uwtr 1 8,500.00 FMD
AB123456 A004 Client 1 7,000.00 CBX
AB123456 A004 Uwtr 1 -6,500.00 CBX

SELECT Policy, Company, Sum(Amount)
FROM MyTable
GROUP BY Policy, Company

Returns:

AB123456 Client 1 500.00
AB123456 Uwtr 1 -900.00

If I add grouping on the created_by field I wont get the answers I want.

Thanks for the help.

 

[Jon K]

You can create a public function and use it in your aggregate query to get the most common Created_By ID for each group of Policy, Company.

Since an aggregate query can return only one record for each group of Policy, Company, when there is a tie, you can only arbitrarily return one of the Created_By IDs.