Menu
I have a column in mysql table that has the the data type INT(11).
How can I search to get the top 10 most occurring values in this column?
922
SQL SELECT COUNT() can be clubbed with SQL WHERE clause. Using the WHERE clause, we have access to restrict the data to be fed to the COUNT() function and SELECT statement through a condition.
To count the top 10 most occurring values in a column in MySQL, The syntax is as follows − To understand the above syntax, let us create a table. The query to create a table is as follows − Insert some records in the table using insert command. The query is as follows − Now you can Display all records from the table using select statement.
The following is the query to select top 10 most occurring values in a column in MySQL − mysql> SELECT Value, count(*) -> FROM countTop10Demo -> GROUP BY Value -> ORDER BY count(*) DESC -> LIMIT 10; Here is the output −
To get 5, 10 or N most frequent values in a single column we can use method value_counts: 5.50 4685 5.60 3967 5.70 3079 5.80 2346 5.90 1947 6.00 1580 .... Name: Magnitude, dtype: int64 To get the N most frequent values only without the count we can use: To get the single most frequent value from value_counts we can combine it with idmax:
This ranks the users by row count and returns the top row. Top User = FIRSTNONBLANK ( TOPN ( 1, VALUES ( UserTableName [UserColumnName] ), RANKX ( ALL ( UserTableName [UserColumnName] ), [Row Count],,ASC) ), 1 ) 05-04-2017 03:42 PM Hi Daemetius. So there's a way to do this using Top N, First Non Blank, and Rank X.
SELECT col, count(*)
FROM tablethingie
GROUP BY col
ORDER BY count(*) DESC
LIMIT 10
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
