Finding "mode" of a data base column...

[DT2000]

I have been looking through the variables and types table in help section of the IDE but have not been able to find what I am after. I am looking to write a formula to get the "mode" and or "multimode" using a column containing numbers (grades), in a database. Can someone point the way to the proper structure to do this?

Perhaps a small example of how to write a formula to obtain the "multimode" or point to some literature to help me use the correct syntax?

I am trying to determine the most entered numbers in a database column. As an example the column will be constantly growing with student grades and I am looking to write a procedure to tell me the (multimode), most listed 6 or 8 numbers (grades) that are listed in the database column.

[skywalk]

You can return the column to an array. Then sort it and count members like a histogram.

[DT2000]

So to understand this correctly (newbie), I would call the DB and then collect the data in the desired column and place it in an array then sort it to find the most entered number(s). Once it is loaded into an array there must be, or I hope there would be, an expression that can be used to get the "multimode" (or similar histogram), of the numbers now in the array.

I'll give that a try and see how I make out.

Thank you for the hint.

[normeus]

@DT2000,
The reason you use a database is to be able to do this type of searches without extra code.
What you are looking for is an SQL command that will find MODE and MULTYMODE;

Example from Helpfile:

Code: Select all

;
; ------------------------------------------------------------
;
;   PureBasic - Database example file
;
;    (c) Fantaisie Software
;
; ------------------------------------------------------------
;

UseSQLiteDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
   Result = DatabaseUpdate(Database, Query$)
   If Result = 0
      Debug DatabaseError()
   EndIf
   
   ProcedureReturn Result
EndProcedure

DatabaseFile$ = GetTemporaryDirectory()+"Database.sqlite"

If CreateFile(0, DatabaseFile$)
   CloseFile(0)
   
   If OpenDatabase(0, DatabaseFile$, "", "")
   
      CheckDatabaseUpdate(0, "CREATE TABLE food (name CHAR(50), weight INT)")

      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('apple', '10')")
      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('pear', '10')")
      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('banana', '20')")
      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('banane', '12')")
      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('banani', '11')")
      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('banano', '10')")
      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('bananu', '12')")
      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('banany', '12')")
      
      
      ;If DatabaseQuery(0, "SELECT * FROM food WHERE weight > 7") ;original SQL
      If DatabaseQuery(0, "SELECT weight, occurs FROM (SELECT weight, count(*) as occurs FROM food GROUP BY `weight`) S1 WHERE occurs = (select max(occurs) FROM ( SELECT `weight`, count(*) as occurs FROM food GROUP BY `weight`) onese )")
         While NextDatabaseRow(0)
            Debug GetDatabaseString(0, 0)
         Wend
      
         FinishDatabaseQuery(0)
      EndIf
      
      CloseDatabase(0)
   Else
      Debug "Can't open database !"
   EndIf
Else
   Debug "Can't create the database file !"
EndIf

Once SQL does the hard work then you can pick the output records.
(S1 and onese are just random names )

Search stackoverflow mysql forums for help on SQL commands.

Norm.

[DT2000]

The example is very much appreciated Norm. I was just in the midst of writing the procedure to call the data from the database column and loaded it to an array.
I will load your example into the IDE and give it a run and see what is happening in the code so I can follow the flow and see how I can use a similar approach.

I will do the search on the mysql forums for help on SQL commands as you suggested.

I appreciate the hints and help... it will certainly assist me in make my little code a more complete and useful project.