0

For a new version of a website, I am making a "Top User" section on the homepage. This allows people to vote for the user on a bunch of different categories. All this information is stored in a MySQL database in two main tables. One table has the information(id*autogenerated*, Username, Date added, etc.) and the other has the rates (id*autogenerated*, linkID, rowAvg, avg, avg2, etc.). The linkID from the rate table corresponds to the id from the information table. How the query works is it queries through the rate_table, orders it by highest averages then selects the first 10 results. Then using the linkID of that row, I select the actual information for that user from the info_table. This is fine and dandy as long as each user only has 1 rate. What happens when a user has 2 or more rates, and both of the rates are positive, is the query selects both of those and pulls the information for each of the 2 rows which is then displayed as two different users even though it is the same person. How can I make the rate_table query know when there are multiple rates for the same linkID and average them together instead of showing them as two different people. I was thinking of inserting each linkID into an array, then for each subsequently selected row, check if that linkID is already in the array. If not, insert it, if it is, then average them together and store it in the array and use the array to populate the table on the homepage. I feel like I am overthinking this. Sorry if this is a little confusing. If you need more information, let me know.

P.S. I'm still learning my way around MySQL queries so I apologize if I am going about this the completely wrong way and you spent the last few minutes trying to understand what I was saying :P

P.P.S. I know I shouldn't be using MySQL_Query anymore, and should be doing prepared statements. I want to master MySQL queries because that is what FMDB databases for iOS use then I will move onto learning prepared statements.

Michael Irigoyen
  • 22,513
  • 17
  • 89
  • 131
Joe Torraca
  • 1,949
  • 5
  • 31
  • 50
  • 1
    This may help point you in the correct direction [MySQL Aggregate Functions](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html). `AVG()` may be what you're looking for. (The average of one result is just that result, so that should still work) – Aiias Mar 24 '13 at 03:46
  • This looks like it would help, but how would the query know which rows to run the `AVG()` function on? Is there a function that detects duplicate values for a column? – Joe Torraca Mar 24 '13 at 03:52
  • 2
    Use the [`GROUP BY`](http://dev.mysql.com/doc/refman/5.0/en/select.html) clause on the column that should be unique. – Aiias Mar 24 '13 at 03:59
  • Awesome! Thanks, that definitely points me in the right direction. If you submit this as an answer I'll accept it – Joe Torraca Mar 24 '13 at 04:02

1 Answers1

0

Take a look at some of the MySQL Aggregate Functions.

AVG() may be what you're looking for. The average of one result is just that result, so that should still work. Use the GROUP BY clause on the column that should be unique in order to run the aggregate calculation on the grouped rows.

Aiias
  • 4,683
  • 1
  • 18
  • 34
  • One last quick question, since I am using a LIMIT 10 function, if there are two rows, it merges those two but only shows the other 9 rows. It isn't showing a full 10 anymore. How do I force it to select one more row if some of them are merged? – Joe Torraca Mar 24 '13 at 04:10
  • @JoeTorraca - This is not a super simple calculation to run in MySQL - Take a look at this existing question about that topic: http://stackoverflow.com/questions/2643314/mysql-group-by-limit. – Aiias Mar 24 '13 at 04:16