How to Calculate the Mode of SQL
In statistical analysis, the mode is the item that occurs most frequently in a group. If, for instance, you are sorting a bag of M&Ms by color, you may find you have 25 red, 13 yellow, 15 brown and 32 green candies. The mode would be green.
When mining information from an SQL database, it is often necessary to calculate statistics based on the information you retrieve. SQL is not meant to be a robust platform for statistical analysis but, by carefully crafting your database query, you can perform many statistical calculations, including the mode.
Other People Are Reading
Instructions
-
-
1
Determine the database tables and field names containing your raw information. For this exercise, presume we have cataloged all the members of a college class in a table named "classroom" which contains the fields "name," "age" and "key." An output of the entire table looks like this:
The table: classroom
key name age
----------------------------------------
7 Steve 21
4 Mark 20
5 Jane 20
6 Fred 20
3 Matt 19
1 John 18
2 Mary 18We want to find the mode of the students' ages.
-
2
Write a basic SQL query to gather the "age" information. The query will look like this:
SELECT age
FROM classroom
GROUP BY age -
-
3
Add the count() function to your query, which counts the number of times a particular item occurs and displays the results in a column named "frequency." Your query should now look like this:
SELECT COUNT( * ) frequency, age
FROM classroom
GROUP BY age -
4
Add the "order by" command to the last line of your query. This will display the "frequency" data from highest to lowest. Your query should look like this:
SELECT COUNT( * ) frequency, age
FROM classroom
GROUP BY age
ORDER BY COUNT( * ) DESC -
5
Insert "TOP 1" to the query after "SELECT." This way, your completed query will return only the first row of data. The complete query should look like this:
SELECT TOP 1 COUNT( * ) frequency, age
FROM classroom
GROUP BY age
ORDER BY COUNT( * ) DESC -
6
Submit this query to the database. Your results based on this example should look like this:
frequency age
------------------------
3 20The mode, in this case, is 20.
-
1
Tips & Warnings
MySQL is a commonly used open-source SQL database. MySQL, however, does not recognize the "TOP 1" command. To execute this query on a MySQL or Postgre SQL database, you must use the "LIMIT" clause, as follows:
SELECT COUNT( * ) frequency, age
FROM classroom
GROUP BY age
ORDER BY COUNT( * ) DESC
LIMIT 1
References
- Photo Credit computer image by blaine stiger from Fotolia.com
