Android Question Get MODE Average from SQL Database?

[Danamo]

Can anyone show me an example of how to get the statistical mode (most frequent value) from an SQL database column?

For example, let Sample be a column of sample values in Table, something like:
2,5,6,2,3,5,3,2,6,1
then the mode would be 2.

I found this example on the web which I tried to apply to my own code, but doing the query returns an error in the "Sample LIMIT 1" part

Dim query As String
    Dim result As Int
    query = "SELECT Sample AS Mode FROM Table GROUP BY 1 ORDER BY COUNT(1) Sample LIMIT 1"
    result = SQL1.ExecQuerysingleresult(query)
    Log("Mode (Sample) = " & result)

With trial and error I found that

query = "SELECT Sample as Mode FROM Table GROUP BY 1 ORDER BY COUNT(1) LIMIT 1"

doesn't cause an error, but it returns the least frequent value (1), not the mode frequent (2), from the column?

I'll be the first to admit I'm clueless here. But in compensation I often DONATE to the one with the most helpful solution to my questions - without insulting or criticizing me for not being smart enough to write my own code

 

[Peter Simpson]

Try this query it should work for you, BUT IT HAS NOT BEEN TESTED.

SELECT Sample AS `Mode`, COUNT(`Sample`) AS `Mode_Frequency` 
FROM `Table`
GROUP BY `Mode`
ORDER BY `Mode_Frequency` DESC
LIMIT    1;

Enjoy...

 

[Danamo]

Try this it should work for you, BUT IT HAS NOT BEEN TESTED.

SELECT Sample AS `Mode`, COUNT(`Sample`) AS `Mode_Frequent`
FROM `Table`
GROUP BY `Mode`
ORDER BY `Mode_Frequent` DESC
LIMIT    1;

Brilliant! This seems to work exactly as needed. My sample database is too large for me to manually compute, but the results are about what I would expect based on other factors.

How may I make a donation in appreciation of your assistance?

 

[Peter Simpson]

I'm pleased that it worked for you as I'm currently on a tablet and couldn't test it. I do not want anything from you except for you to learn from the community and to enjoy using B4A

 

[Danamo]

I'm pleased that it worked for you as I'm currently on a tablet and couldn't test it. I do not want anything from you except for you to learn from the community and to enjoy using B4A

Well, Thank You Very Much! I am indeed learning, and very much enjoying B4A.

 

[BillMeyer]

I'm pleased that it worked for you as I'm currently on a tablet and couldn't test it. I do not want anything from you except for you to learn from the community and to enjoy using B4A

A true B4X Gentleman !!

 

[Mahares]

query = "SELECT Sample as Mode FROM Table GROUP BY 1 ORDER BY COUNT(1) LIMIT 1"

Although @Peter Simpson hit the nail on the head, your code is very close. All you needed to do in your statement is add DESC to the query as shown below. There are different ways to skin a cat. Also, avoid naming a table Table because it is a reserved word:

query = "SELECT Sample as Mode FROM Table1 GROUP BY 1 ORDER BY COUNT(1) DESC LIMIT 1"

 

[LucaMs]

There are different ways to skin a cat.

 

[Danamo]

Although @Peter Simpson hit the nail on the head, your code is very close. All you needed to do in your statement is add DESC to the query as shown below. There are different ways to skin a cat. Also, avoid naming a table Table because it is a reserved word:

query = "SELECT Sample as Mode FROM Table1 GROUP BY 1 ORDER BY COUNT(1) DESC LIMIT 1"

Thanks for your input. I obviously have much yet to learn! One thing I do know, however, is that there are multiple ways to approach a coding solution. And some are more elegant than others. Mine are probably pretty clumsy since I'm a novice.

Incidentally, the sample code I posted was just to illustrate generically what I was after. Table and Sample are not the actual names of the table and column in my code.