M-Geek
asked on
Mysql - How to get the top 5 values from 3 different columns
Hi Exparts,
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
Corolla (4)
Civic (3)
Focus (2)
Accord (1)
Fusion (1)
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.
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
Corolla (4)
Civic (3)
Focus (2)
Accord (1)
Fusion (1)
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT COUNT(*) FROM usermodel GROUP BY modelID
But don't use the above SQL Statement. That is only if you optimized your tables.
ALSO this will allow you to add practically unlimited amounts of cars TO any USER
All in all it is the best practice alteration.
Sincerely,
=NerdsOfTech
ALSO this will allow you to add practically unlimited amounts of cars TO any USER
All in all it is the best practice alteration.
Sincerely,
=NerdsOfTech
You could also JOIN table:model to get model names on top of that...
The possibilities are much, much greater....
The possibilities are much, much greater....
Less repeated/manual data = less errors
ASKER
Thanks guys... Although majkiw gave me an straight answer to the question... CDagusting and Nerdsoftech pointed me in the correct direction which in the long term will be really helpful as opposed to a quick way out.
I appreciate all of your answers and the time spent in replying back to it.
Thanks a lot.
I appreciate all of your answers and the time spent in replying back to it.
Thanks a lot.
For future scalability and speed, you should deeply consider this alteration to your database in the future, especially if the database will increase in size significantly.
=NerdsOfTech