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
Model | ProblemCount | MostCommonProblemType | ProblemCount |
---|---|---|---|
Ford Mustang | 8 | Engine | 4 |
Ford Focus | 6 | Engine | 3 |
Ford Puma | 2 | Maintenance | 2 |
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
Convert String to Date in Ms Access Query
Find Out Where MySQL Is Installed on MAC Os X
Check If a Row Exists, Otherwise Insert
Sql Query for Values Consisting of Only a Specific Character
How to Insert an Image in Sqlite Database(Table)
Return Dummy Value If No Row Present for in Clause
Sql Call Stored Procedure for Each Row Without Using a Cursor
How to Change Date Format in .Net Rdlc Report
Convert a 12 Hour Format to 24 Hour Format in SQL Server
Loop Through Table Rows and Call Stored Procedure on Every Row
Select Query to Remove Non-Numeric Characters
Why Postgresql Does Not Like Uppercase Table Names
Concat(Column) Over(Partition By...)? Group-Concatentating Rows Without Grouping the Result Itself
Trim Whitespaces (New Line and Tab Space) in a String in Oracle
State Wise Data of Country and City