Mysql - How to get the top 5 values from 3 different columns
Posted on 2009-04-29
First of all thanks for taking the time to read my question.
Here's the case I'm a total noob in mysql and I'm learning pretty much by examples, here I want to get the top 5 values from a table based on the number of times a value appears oin three different columns. Let me put it in 1 example:
Let's say I have a table for people who like cars and in my site they can pick their favorite cars from 3 different brands (ie. Toyota, Honda and Ford).
Under each brand (by using a dropdown menu) they have all the different cars from each brand... for honda for example they will have civic, accord, CR-V, etc. After submitting the form this information is saved in the database under the brand rows
The table is users and the columns for each row are ID, username, b_Ford, b_Honda and b_Toyota.
So the actual data would look like this:
1, john, Civic, Explorer, Corolla
2, james, accord, fusion, corolla
3, Lynda, civic, focus, corolla
4, Bryan, civic, focus, corolla
I need help writting a query that will give me the top 5 favorite car accorss all different brands selecting the values from b_Ford, b_Honda and b_Toyota.
In the case of this example the output would be as follows
I managed to get the top 5 from each brand using SELECT b_Ford, COUNT(*) FROM users GROUP BY b_Ford;
But I actually need the top 10 across all different brands.
Can you Help?
Thanks in advance.