Page 1 of 1
Finding "mode" of a data base column...
Posted: Wed Sep 20, 2017 11:28 pm
by 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.
Re: Finding "mode" of a data base column...
Posted: Thu Sep 21, 2017 12:33 am
by skywalk
You can return the column to an array. Then sort it and count members like a histogram.
Re: Finding "mode" of a data base column...
Posted: Thu Sep 21, 2017 1:59 am
by 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.
Re: Finding "mode" of a data base column...
Posted: Thu Sep 21, 2017 2:22 am
by 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.
Re: Finding "mode" of a data base column...
Posted: Thu Sep 21, 2017 3:38 am
by 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.