Get Most Common Value for Each Value of Another Column in SQL

Find most frequent value in SQL column

SELECT
  <column_name>,
  COUNT(<column_name>) AS `value_occurrence` 

FROM
  <my_table>

GROUP BY 
  <column_name>

ORDER BY 
  `value_occurrence` DESC

LIMIT 1;

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

SQL: Returning the most common value for each person

Preliminary comment

Please learn to use the explicit JOIN notation, not the old (pre-1992) implicit join notation.

Old style:

SELECT transactionTable.rating as MostCommonRating 
FROM personTable, transactionTable 
WHERE personTable.transactionid = transactionTable.transactionid 
AND personTable.personid = 1
GROUP BY transactionTable.rating 
ORDER BY COUNT(transactionTable.rating) desc 
LIMIT 1

Preferred style:

SELECT transactionTable.rating AS MostCommonRating 
  FROM personTable
  JOIN transactionTable 
    ON personTable.transactionid = transactionTable.transactionid 
 WHERE personTable.personid = 1
 GROUP BY transactionTable.rating 
 ORDER BY COUNT(transactionTable.rating) desc 
 LIMIT 1

You need an ON condition for each JOIN.

Also, the personID values in the data are strings, not numbers, so you'd need to write

 WHERE personTable.personid = "Ben"

for example, to get the query to work on the tables shown.

Main answer

You're seeking to find an aggregate of an aggregate: in this case, the maximum of a count. So, any general solution is going to involve both MAX and COUNT. You can't apply MAX directly to COUNT, but you can apply MAX to a column from a sub-query where the column happens to be a COUNT.

Build the query up using Test-Driven Query Design — TDQD.

Select person and transaction rating

SELECT p.PersonID, t.Rating, t.TransactionID
  FROM PersonTable AS p
  JOIN TransactionTable AS t
    ON p.TransactionID = t.TransactionID

Select person, rating, and number of occurrences of rating

SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
  FROM PersonTable AS p
  JOIN TransactionTable AS t
    ON p.TransactionID = t.TransactionID
 GROUP BY p.PersonID, t.Rating

This result will become a sub-query.

Find the maximum number of times the person gets any rating

SELECT s.PersonID, MAX(s.RatingCount)
  FROM (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
          FROM PersonTable AS p
          JOIN TransactionTable AS t
            ON p.TransactionID = t.TransactionID
         GROUP BY p.PersonID, t.Rating
       ) AS s
 GROUP BY s.PersonID

Now we know which is the maximum count for each person.

Required result

To get the result, we need to select the rows from the sub-query which have the maximum count. Note that if someone has 2 Good and 2 Bad ratings (and 2 is the maximum number of ratings of the same type for that person), then two records will be shown for that person.

SELECT s.PersonID, s.Rating
  FROM (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
          FROM PersonTable AS p
          JOIN TransactionTable AS t
            ON p.TransactionID = t.TransactionID
         GROUP BY p.PersonID, t.Rating
       ) AS s
  JOIN (SELECT s.PersonID, MAX(s.RatingCount) AS MaxRatingCount
          FROM (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
                  FROM PersonTable AS p
                  JOIN TransactionTable AS t
                    ON p.TransactionID = t.TransactionID
                 GROUP BY p.PersonID, t.Rating
               ) AS s
         GROUP BY s.PersonID
       ) AS m
    ON s.PersonID = m.PersonID AND s.RatingCount = m.MaxRatingCount

If you want the actual rating count too, that's easily selected.

That's a fairly complex piece of SQL. I would hate to try writing that from scratch. Indeed, I probably wouldn't bother; I'd develop it step-by-step, more or less as shown. But because we've debugged the sub-queries before we use them in bigger expressions, we can be confident of the answer.

WITH clause

Note that Standard SQL provides a WITH clause that prefixes a SELECT statement, naming a sub-query. (It can also be used for recursive queries, but we aren't needing that here.)

WITH RatingList AS
     (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
        FROM PersonTable AS p
        JOIN TransactionTable AS t
          ON p.TransactionID = t.TransactionID
       GROUP BY p.PersonID, t.Rating
     )
SELECT s.PersonID, s.Rating
  FROM RatingList AS s
  JOIN (SELECT s.PersonID, MAX(s.RatingCount) AS MaxRatingCount
          FROM RatingList AS s
         GROUP BY s.PersonID
       ) AS m
    ON s.PersonID = m.PersonID AND s.RatingCount = m.MaxRatingCount

This is simpler to write. Unfortunately, MySQL does not yet support the WITH clause.

The SQL above has now been tested against IBM Informix Dynamic Server 11.70.FC2 running on Mac OS X 10.7.4. That test exposed the problem diagnosed in the preliminary comment. The SQL for the main answer worked correctly without needing to be changed.

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().

How to find the most common value in each column

First create a CTE that uses COUNT() window function to return the number of times each name occurs in each category and then use FIRST_VALUE() window function to get for each column the name that occurs the most:

WITH cte AS (
  SELECT *,
         COUNT(*) OVER (PARTITION BY category, name1) count1,
         COUNT(*) OVER (PARTITION BY category, name2) count2,
         COUNT(*) OVER (PARTITION BY category, name3) count3,
         COUNT(*) OVER (PARTITION BY category, name4) count4,
         COUNT(*) OVER (PARTITION BY category, name5) count5
  FROM tablename
)
SELECT DISTINCT category,
       FIRST_VALUE(name1) OVER (PARTITION BY category ORDER BY count1 DESC) name1,
       FIRST_VALUE(name2) OVER (PARTITION BY category ORDER BY count2 DESC) name2,
       FIRST_VALUE(name3) OVER (PARTITION BY category ORDER BY count3 DESC) name3,
       FIRST_VALUE(name4) OVER (PARTITION BY category ORDER BY count4 DESC) name4,
       FIRST_VALUE(name5) OVER (PARTITION BY category ORDER BY count5 DESC) name5
FROM cte

See the demo.

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:

select payee, count(*), category 
from transactions t
group by payee, category 
having count(*) = (
    select count(*) 
    from transactions t1 
    where t1.payee = t.payee 
    group by category
    order by count(*) desc limit 1
)
order by count(*) desc

payee   | count(*) | category
:------ | -------: | :-------
Amazon  |        3 | Gifts   
Alibaba |        2 | Stock   

select payee, cnt, category
from (
    select 
        payee, 
        count(*) cnt, 
        category, 
        rank() over(partition by payee order by count(*) desc) rn
    from transactions 
    group by category, payee
) t
where rn = 1