How to count frequen data in database

[achotto]

hii..
i got a really have kinda complicated problem.

this is my table

table participate
| id |phone_no|vote1|vote2|vote3 |vote4|vote5|status|
|_1_| +012++_|_1__|__3__|__4__|__5_|___4_|__5_|
cont another data..

the situation is like this, i already got the most frequent data that had been inserted from vote1 to vote 5,and the result is 4example.

now comes the problems,

i need to detect the phone_no(can be repeated on the same field) that had the biggest data with values 4 on vote1 to vote 5. with rules that the status must be 5 or the vote will not be count.

please help me.. i've been stuck here about a weeks.
pls4xx...

[SLauren]

If you already have the result, then the following query might help:

SELECT phone_no FROM Participate WHERE status = 5 and result IN (GREATEST(vote1,vote2,vote3,vote4,vote5));

Hope this could help.

Thanks,
Lauren

[achotto]

thank you so much for reply..

i'm so sorry, this is my bad.
what i want is to retrieve the phone no that has a most frequent value 4 in vote1 to vote5. and how much it had it.

please help me..

[SLauren]

Can you add one more column to your table, say "occurrence" with a default value of zero.
If you already have the result with you, then you can try the following query which will update your occurrence column and get the phone number which will have maximum number of occurrences for the result:

UPDATE participate SET occurrence = IF(vote1 <=  result,vote1 div result,0) + if(vote2 <=  result,vote2 div result,0) + if(vote3 <= result,vote3 div result,0) + if(vote4 <= result,vote4 div result,0) + if(vote5 <= result,vote5 div result,0);

// Declare an <int> variable to hold the maximum count for the result, say "maxOccurrences".

SELECT MAX(occurrence)  INTO maxOccurrences from Participate;
SELECT phone_no FROM Participate WHERE status = 5 AND occurrence = maxOccurrences;

Hope this could help.
Thanks,
Lauren