CODEWITHSUNDEEP
mysqlsqldatabasems-access
Horthe92
Horthe92

Reputation: 53

SQL: Get the most frequent value for each group

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`

Upvotes: 5

Views: 14351

Answers (2)

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

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

Upvotes: 1

Parfait
Parfait

Reputation: 107767

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

Upvotes: 6

Related Questions