Link to home
Start Free TrialLog in
Avatar of M-Geek
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.


SOLUTION
Avatar of majkiw
majkiw

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NerdsOfTech
I agree with cdaugustin with the best practice methods suggestion he made - for relational database design.

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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
You could also JOIN table:model to get model names on top of that...

The possibilities are much, much greater....
Less repeated/manual data = less errors
Avatar of M-Geek
M-Geek

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.