Mysql - How to get the top 5 values from 3 different columns

Posted on 2009-04-29
Last Modified: 2013-12-12
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.

Question by:M-Geek
    LVL 2

    Assisted Solution

    So, you have to select like that for every type of car, then using UNION you make one of those three (remember of changing the names of columns with 'AS') and you just order this descending by numbers (ORDER BY numbers DESC) and with grab only first 5 results (LIMIT 0, 5)
    (SELECT b_Ford AS brand, COUNT(*) AS number FROM users GROUP BY b_Ford) UNION 
    (SELECT b_Toyota AS brand, COUNT(*) AS number FROM users GROUP BY b_Toyota) 
    (SELECT b_Honda AS brand, COUNT(*) AS number FROM users GROUP BY b_Honda)
    ) AS best_cars 
    ORDER BY number DESC LIMIT 0,5

    Open in new window

    LVL 5

    Assisted Solution


    while your request can be solved with either the solution posted above OR even  temporary tables (by putting the outputs from three sqls like the one youve posted into the temporary table and then doing a final select in the temp table with a sorting) I for one do not agree with such  (ab)use of a db engine but rather il explain to you why you should not have the problem in the first place.

    On your problem you have two sets, MAKES(honda, toyota etc) and MODELS(corrola, fusion, etc).

    Since the relationship between the two is 1->n (1 make to n models) the correct thing to do would be to have two tables: One table containing the MAKES(makeId++, makeName), another containing the MODELS (with an ID linking each to their MAKE)(modelId++, makeId, modelName).

    To close the circle there is another 1->n relationship between the USER and the MODELS, for this you would also need to tables: one that contains the USERs (which you presumably already have) and one that contains the linking USERS->MODELS (usrToModels ++, userId, modelId)(with an index on userId&modelId)
    (++ means autoincrement)

    Your SQL would then be against the USERS->MODELS table where you would simply SELECT COUNT(*) and  GROUP BY modelId and SORT, just like you did in your queries (you can add a subquery or JOIN for the model name too).

    I hope this small bit of db tips in a nutshell makes sense.
    LVL 19

    Expert Comment

    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.

    LVL 19

    Accepted Solution

    table: user
    fields: userID, Name
    1, john
    2, james
    3, Lynda
    4, Bryan

    table: make
    fields: makeID, makeName
    1, Honda
    2, Toyota
    3, Ford

    table: model
    fields: modelID, modelName
    1, Corolla
    2, Civic
    3, Focus
    4, Accord
    5, Fusion
    6, Explorer

    table: usermodel
    fields: userID, modelID
    1, 2
    1, 6
    1, 1
    2, 4
    2, 3
    2, 1
    3, 2
    3, 3
    3, 1
    4, 2
    4, 3
    4, 1

    table: makemodel
    fields: makeID, modelID
    1, 2
    1, 4
    2, 1
    3, 3
    3, 5
    3, 6

    The count SQL would be just like cdaugustin suggested in that case:
    SELECT COUNT(*) and  GROUP BY modelId

    Very nice.
    LVL 19

    Expert Comment

    SELECT COUNT(*) FROM usermodel GROUP BY modelID
    LVL 19

    Expert Comment

    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.

    LVL 19

    Expert Comment

    You could also JOIN table:model to get model names on top of that...

    The possibilities are much, much greater....
    LVL 19

    Expert Comment

    Less repeated/manual data = less errors
    LVL 1

    Author Closing Comment

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now