Sql Server Count Instances of Most Frequent Data

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 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

Count number of occurrences for each unique value

Yes, you can use GROUP BY:

SELECT time,
activities,
COUNT(*)
FROM table
GROUP BY time, activities;

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































ModelProblemCountMostCommonProblemTypeProblemCount
Ford Mustang8Engine4
Ford Focus6Engine3
Ford Puma2Maintenance2

how to select the most frequently appearing values?

select
x.last_name,
x.name_count
from
(select
u.last_name,
count(*) as name_count,
rank() over (order by count(*) desc) as rank
from
users u
group by
u.last_name) x
where
x.rank = 1

Use the analytical function rank. It will assign a numbering based on the order of count(*) desc. If two names got the same count, they get the same rank, and the next number is skipped (so you might get rows having ranks 1, 1 and 3). dense_rank is an alternative which doesn't skip the next number if two rows got the same rank, (so you'd get 1, 1, 2), but if you want only the rows with rank 1, there is not much of a difference.

If you want only one row, you'd want each row to have a different number. In that case, use row_number. Apart from this small-but-important difference, these functions are similar and can be used in the same way.



Related Topics



Leave a reply



Submit