CODEWITHSUNDEEP
sqlms-access
bumbur
bumbur

Reputation: 311

Find most frequent record in WHOLE table

In Microsoft Access 2010 I have a table that looks like this (of course it's much bigger):

Date           A         B         C         D         E           F
2008-01-02    25        24        27        25        65          34
2008-01-03    28        25        38        10        43          25
2008-01-04    14        16        25         9        22          55
2008-01-05    25        16        11        25        99          44
2008-01-06    21        16        11        25        25          98

And I have to find the value that occurs most frequently in all columns (one number) and tell the number of its occurs ('Date' is not important).

So in this example it would be 25, which occurs 9 times.

Could you help me, because I have no idea how to do it.

Upvotes: 0

Views: 121

Answers (1)

Strawberry
Strawberry

Reputation: 33935

SELECT val, COUNT(*) ttl FROM
(
SELECT date, 'A' type, a val FROM my_table
UNION ALL
SELECT date, 'B', b val FROM my_table
UNION ALL
SELECT date, 'C', c val FROM my_table
UNION ALL
SELECT date, 'D', d val FROM my_table
UNION ALL
SELECT date, 'E', e val FROM my_table
UNION ALL
SELECT date, 'F', f val FROM my_table
) x
GROUP BY val ORDER BY ttl DESC LIMIT 1;

This is a generic solution, so some parts of it are actually superfluous to this specific problem.

Oh, this solution for MySQL, but I imagine you can adapt it.

Upvotes: 1

Related Questions