Find Most Frequent Value in SQL Column

Find most frequent value in SQL column

SELECT
  ,
  COUNT() AS `value_occurrence` 

FROM
  

GROUP BY 
  

ORDER BY 
  `value_occurrence` DESC

LIMIT 1;

Replace and . Increase 1 if you want to see the N most common values of the column.

Find the most frequent value per group in a table column

Updated: Fiddle

This should address the specific "which object per ethnicity" question.

Note, this doesn't address ties in the count. That wasn't part of the question / request.

Adjust your SQL to include this logic, to provide that detail:

WITH cte AS (
        SELECT officer_defined_ethnicity
             , object_of_search
             , COUNT(*) AS n
             , ROW_NUMBER() OVER (PARTITION BY officer_defined_ethnicity ORDER BY COUNT(*) DESC) AS rn
          FROM stopAndSearches
         GROUP BY officer_defined_ethnicity, object_of_search
     )
SELECT * FROM cte
 WHERE rn = 1
;

Result:

officer_defined_ethnicity	object_of_search	n	rn
ethnicity1	Cat	1	1
ethnicity2	Stolen goods	2	1
ethnicity3	Fireworks	1	1

How to select top 5 most frequent value in a column

MySQL's and SQLite's LIMIT clauses both lack a WITH TIES option, which is what you'd need here. So use a subquery instead: Select the five greatest numbers of tracks (which is a rare case where you actually combine GROUP BY with DISTINCT - GROUP BY to get counts per album, DISTINCT to get the five highes different counts), then select the albums having as many tracks. As this is about an aggregation result, this belongs in the HAVING clause:

SELECT
  albums.title AS Album,
  artists.name AS Artist,
  COUNT(*) as TitleCount
FROM tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
INNER JOIN artists ON artists.artistid = albums.artistid
GROUP BY albums.albumid 
HAVING COUNT(*) IN
(
  SELECT DISTINCT COUNT(*)
  FROM tracks
  GROUP BY albumid
  ORDER BY count(*) DESC
  LIMIT 5
)
ORDER BY TitleCount DESC;

Getting the most frequent value in a column for a specific person

Try this:

SELECT player, SUM(kills), SUM(deaths), SUM(assists), frequency
FROM
(
    SELECT player, SUM(kills) AS kills, SUM(deaths) AS deaths, SUM(assists) AS assists, champSelection, COUNT(*) AS frequency
    FROM playerTable
    GROUP BY player, champSelection
    ORDER BY frequency DESC
) AS inner_table
GROUP BY player

Get most frequent value with SQL query

The ANSI SQL syntax would be:

SELECT GENRE, COUNT(*) AS Frequency
FROM BooksRead
GROUP BY GENRE
ORDER BY COUNT(*) DESC
FETCH FIRST 1 ROW ONLY;

Not all databases support that syntax. Many support LIMIT:

SELECT GENRE, COUNT(*) AS Frequency
FROM BooksRead
GROUP BY GENRE
ORDER BY COUNT(*) DESC
LIMIT 1;

However, the exact syntax depends on the database you are using.

You can also use ANSI standard window functions:

SELECT *
FROM (SELECT GENRE, COUNT(*) AS Frequency,
             ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM BooksRead
      GROUP BY GENRE
     ) g
WHERE seqnum = 1;

If you want ties then use RANK() instead of ROW_NUMBER().

Occurrence of the most frequent value in mysql

I used, as you, that strange feature of mysql, using a column that is not grouped and not aggregated, and made the needed subselect:

SELECT c.Model, 
COUNT(j.JobType) AS ProblemCount,
j.JobType as MostCommonProblemType
, (select count(*) from Jobs where jobs.Jobtype = j.JobType 
   and CarId in (select Id from Cars c2 where c2.model = c.model) 
  ) as ProblemCount
FROM Cars c
INNER JOIN Jobs j ON j.CarId = c.Id 
GROUP BY c.Model
ORDER BY COUNT(j.JobType) DESC;

Output

Model	ProblemCount	MostCommonProblemType	ProblemCount
Ford Mustang	8	Engine	4
Ford Focus	6	Engine	3
Ford Puma	2	Maintenance	2

SQL SSMS return most frequent value for each personal id

Assuming you just want to create a report about the favorite fruit per person you can use this query:

with cte as (
    select p_id, fruit_bought, row_number() over (partition by p_id order by count(*) desc) as rn
    from t
    group by p_id, fruit_bought
)
select p_id, fruit_bought as favorite_fruit
from cte
where rn = 1

How to select the second most frequent value in column?

There might be shorter and simpler queries, but this one should work on every DB and prevent issues like the key words LIMIT, TOP etc. can't be used on all different DB's.

SELECT name FROM Countries
GROUP BY name
HAVING COUNT(name) = (SELECT MAX(a.amount) AS amount
FROM (SELECT name, COUNT(name) AS amount
FROM Countries
GROUP BY name) a
WHERE amount < (SELECT MAX(b.amount) FROM
(SELECT name, COUNT(name) AS amount
FROM Countries
GROUP BY name)b));

This query will find step by step the second frequent value of your table using sub queries.

Find Most Frequent Value in SQL Column