[SOLVED] Need help putting together query.

[pocobueno1388]

Okay, I am stumped on how to form my query to get my desired results.

 

I have a table like so:

TABLE 'club_votes'
------------------
vote_for [A players unique ID]
clubID [A clubs unique ID]

 

Here is some example data of what the rows would hold:

Vote_for           ClubID
--------          --------
   1                  1
   10                 1   
   1                  1 
   1                  1 
   10                 2
   5                  2
   6                  2
   6                  2
   1                  2

 

Look at the "vote_for" column, "1" got 4 votes for club 1 since the number appears 4 times.

 

What I am trying to do is display the person who got the most votes for each club.

 

So according to the rows in the table, I would want to output this:

Club           Winner
-----         -------
  1              1
  2              6

 

Any help putting this query together would be greatly appreciated =] Thanks.

[teng84]

select count(distinct(id)) as result, id from tablename group by id result

 

is this what you mean

your example arent clear enough for me coz its not showing the relationship of the table but i guess that will do

 

or tell me if I'm totally wrong

 

edited.....

 

[Illusion]

try this

 

select club_id,vote_for,max(count(vote_for)) as no_of_votes  from table group by club_id,vote_for order by club_id;

[pocobueno1388]

Illusion - I get an error saying "Invalid use of group function".

 

Teng - Yours doesn't work either.

 

[fenway]

You need to use SUM().

[pocobueno1388]

You need to use SUM().

 

Yeah, I was thinking that, but wouldn't that add all the rows together? I just need to count how many rows for each user voted for, so I don't understand how I would use SUM() in the query. I will keep playing with it to see if I can get it to work.

[Illusion]

that error is due to max()

 

ok, u can try this

 

create temporary table temp as select club_id,vote_for,count(vote_for) as votes  from table group by club_id,vote_for order by club_id;

 

select club_id,vote_for,count(vote_for) as cnt  from table group by club_id,vote_for order by club_id having cnt=any(select max(votes) from temp group by club_id);

 

huh.......there might be a alternative way.

[pocobueno1388]

Hmmm, I still can't figure this out =/

 

Illusion - I think I would rather find an alternative to your method...

[fenway]

You need to use SUM().

 

Yeah, I was thinking that, but wouldn't that add all the rows together? I just need to count how many rows for each user voted for, so I don't understand how I would use SUM() in the query. I will keep playing with it to see if I can get it to work.

 

Sorry, I missed the point :-( Each record is a single vote?  Then you need to a do a count() with a group by club_id....

[Barand]

SELECT clubID, vote_for
FROM
(SELECT clubID, vote_for, COUNT(*) FROM votes GROUP BY clubID, vote_for ORDER BY COUNT(*) DESC) as x
GROUP BY clubID

[pocobueno1388]

I'm getting a "Invalid use of group function" error with that query =[

 

SELECT clubID, vote_for
FROM
(SELECT clubID, vote_for, COUNT(*) FROM club_votes GROUP BY clubID, vote_for ORDER BY COUNT(*) DESC) as x
GROUP BY clubID

 

Thank you both so much for helping me

[Barand]

I ran it in MySQL Query Browser before posting and it didn't object ???

 

Try adding COUNT(*)

 

SELECT clubID, vote_for, COUNT(*)
FROM
(SELECT clubID, vote_for, COUNT(*) FROM votes GROUP BY clubID, vote_for ORDER BY COUNT(*) DESC) as x
GROUP BY clubID

[pocobueno1388]

Hmmm....same result =/

[Barand]

What version of MySQL?

[pocobueno1388]

4.1.22

[Barand]

I'm using 5.0.18

 

4.1 gives subqueries so I can't see what it is yet that makes the difference.

[pocobueno1388]

Here is the winning query, thanks to Travis xD

 

SELECT clubID, vote_for FROM (SELECT clubID, vote_for, COUNT(*) as count1 FROM club_votes GROUP BY
clubID, vote_for ORDER BY count1 DESC) as total GROUP BY clubID

 

Thank you to all who helped, it was much appreciated.