Jump to content

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


This topic is 2889 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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"
 ; "..." ; "")
Link to comment
Share on other sites

 

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!!

Link to comment
Share on other sites

This topic is 2889 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.