SQL: Get the most frequent value for each group
12,250
Solution 1
Consider an INNER JOIN
to match the two derived table subqueries rather than a list of subquery select statements matched with WHERE
clause. This has been tested in MS Access:
SELECT MaxCountSub.`Time`, CountSub.`Value`
FROM
(SELECT myTable.`Time`, myTable.`Value`, Count(myTable.`Value`) AS CountOfValue
FROM myTable
GROUP BY myTable.`Time`, myTable.`Value`) As CountSub
INNER JOIN
(SELECT dT.`Time`, Max(CountOfValue) As MaxCountOfValue
FROM
(SELECT myTable.`Time`, myTable.`Value`, Count(myTable.`Value`) AS CountOfValue
FROM myTable
GROUP BY myTable.`Time`, myTable.`Value`) As dT
GROUP BY dT.`Time`) As MaxCountSub
ON CountSub.`Time` = MaxCountSub.`Time`
AND CountSub.CountOfValue = MaxCountSub.MaxCountOfValue
Solution 2
you can do this by query like this:
select time, value
from (select value, time from your_table
group by value , time
order by count(time) desc
) temp where temp.value = value
group by value
Author by
Horthe92
Updated on June 21, 2022Comments
-
Horthe92 almost 2 years
Lets say that I have a table ( MS-ACCESS / MYSQL ) with two columns ( Time 'hh:mm:ss' , Value ) and i want to get most frequent value for each group of row.
for example i have
Time | Value 4:35:49 | 122 4:35:49 | 122 4:35:50 | 121 4:35:50 | 121 4:35:50 | 111 4:35:51 | 122 4:35:51 | 111 4:35:51 | 111 4:35:51 | 132 4:35:51 | 132
And i want to get most frequent value of each Time
Time | Value 4:35:49 | 122 4:35:50 | 121 4:35:51 | 132
Thanks in advance
Remark I need to get the same result of this Excel solution : Get the most frequent value for each group
** MY SQL Solution **
I found a solution(Source) that works fine with mysql but i can't get it to work in ms-access:
select cnt1.`Time`,MAX(cnt1.`Value`) from (select COUNT(*) as total, `Time`,`Value` from `my_table` group by `Time`,`Value`) cnt1, (select MAX(total) as maxtotal from (select COUNT(*) as total, `Time`,`Value` from `my_table` group by `Time`,`Value`) cnt3 ) cnt2 where cnt1.total = cnt2.maxtotal GROUP BY cnt1.`Time`