Calculating maximum occurance of values in all records.

[Deepak Kumar]

Hi all,

 

Scenario: I have Product and Customer table. I have some records in Product table, having  fields, Product_name, Product_Category, Product_customer.  

And also i have some records in Customer table.

 

e.g, Product table having 5 products. I have 3 transport as Product_category, 2 location as Product_category. John, as value in Product_customer field, in all 5 records. 

 

 

Now , i want to calculate the maximum occurrence of Product_Category of a each customer in Customer table.

 

Output should be Transport.

 

Please see screenshot as reference.

[rivet]

1. When you say maximum occurrence, do mean you would like a count for each category?

2. FMP11 or FMP12?

[Deepak Kumar]

It is  fmp12.

 

Suppose there is a customer, John, which has 3 transport and 2 Location as Product category. Since , transport is maximum, i want to to calculate transport / the maximim occurrence of values for a customer in Product_category.

[rivet]

If I understand correctly.

 

1. I would count each category for that client, sort descending using the ExecuteSql function,

2. then take the first line returned with GetValue.

 

 

ExecuteSQL ( "

SELECT

p.category, count ( p.category) AS total

FROM

product p

WHERE

p.id_customer = ? 

GROUP BY

p.category

ORDER BY total DESC

 

 

" ; " " ; "¶" ; customer::id )

 

This will return a sorted array with the highest count at the top of the list, which you can grab with GetValue function.

[Deepak Kumar]

 

Thanks a lot rivet. you solved it.