how can I count occurrence number of each item in value lists

[megalomania]

I'm using FileMakerPro Adv 13

 

Considering There are records like these below

 

1 Orange

2 Apple

3 Strawberry

4 Apple

5 Lemon

6 Apple

7 Orange

8 Melon

 

I have a value lists called "TypeOfFruits"

then If I execute this function

 

Substitute(Substitute ( ValueListItems ( Get ( FileName ); "TypeOfFruits"); "¶"; "n") ;" ";"")

 

I get this output

 

OrangenApplenStrawberrynLemonnMelon

 

It is shown on a page just like this

 

Orange

Apple

Strawberry

Lemon

Melon

 

If I want to count all of them and want an output like this, how can I change my function above?

(sorted by the number of count)

 

3...Apple

2...Orange

1...Strawberry

1...Lemon

1...Melon

[Ocean West]

Try this on http://www.briandunning.com/cf/1289

[Ocean West]

you can always use this function in a script step setting a field. - just change the items in bold your field names and table name.

 

ExecuteSQL ( 

"SELECT typeOfFruits, COUNT(*) FROM sample GROUP BY typeOfFruits ORDER BY 2 DESC"

 ; "" ; "")

or if you want it more in line like you example 

 

3...apple

2...orange

1...strawberry

1...lemon

1...melon

 

ExecuteSQL ( 

"SELECT COUNT(*), typeOfFruits FROM sample GROUP BY typeOfFruits ORDER BY 1 DESC"

 ; "..." ; "")

[megalomania]

Try this on http://www.briandunning.com/cf/1289

 

Thanks for introducing the custom function!!!

 

This is what I wanted!!! Thanks so much!

 

you can always use this function in a script step setting a field. - just change the items in bold your field names and table name.

 

ExecuteSQL ( 

"SELECT typeOfFruits, COUNT(*) FROM sample GROUP BY typeOfFruits ORDER BY 2 DESC"

 ; "" ; "")

or if you want it more in line like you example 

 

3...apple

2...orange

1...strawberry

1...lemon

1...melon

 

ExecuteSQL ( 

"SELECT COUNT(*), typeOfFruits FROM sample GROUP BY typeOfFruits ORDER BY 1 DESC"

 ; "..." ; "")

 

This SQL also looks useful!!

 

Thanks for the advanced technique!!