Writing SQL Query for Getting Maximum Occurrence of a Value in a Column

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.

Finding the maximum occurrence of a column value based on another column in SQL

Try this:

select
    conference_name,
    keyword_name
from (
    select
        conference_name,
        keyword_name,
        rank() over (partition by conference_name order by count(*) desc) rnk
    from your_table
    group by 
        conference_name,
        keyword_name
) t where rnk = 1;

It assigns rank based on number of occurrences of the same keyword within a conference_name and then filters to get the top ranked rows.

Demo @ RexTester

SQL Query To Obtain Value that Occurs more than once

For SQL Server 2005+

;WITH T AS
(
SELECT *, 
       COUNT(*) OVER (PARTITION BY Lastname) as Cnt
FROM Students
)
SELECT * /*TODO: Add column list. Don't use "*"                   */
FROM T
WHERE Cnt >= 3

How to count occurrences of a column value efficiently in SQL?

This should work:

SELECT age, count(age) 
  FROM Students 
 GROUP by age

If you need the id as well you could include the above as a sub query like so:

SELECT S.id, S.age, C.cnt
  FROM Students  S
       INNER JOIN (SELECT age, count(age) as cnt
                     FROM Students 
                    GROUP BY age) C ON S.age = C.age

---

Related Topics