[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-04-29
9
Medium Priority
?
376 Views
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.


0
Comment
Question by:M-Geek
9 Comments
 
LVL 2

Assisted Solution

by:majkiw
majkiw earned 320 total points
ID: 24265329
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 * FROM 
(
(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) 
UNION 
(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

0
 
LVL 5

Assisted Solution

by:cdaugustin
cdaugustin earned 800 total points
ID: 24265516
hi,

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.
0
 
LVL 20

Expert Comment

by:NerdsOfTech
ID: 24266503
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 20

Accepted Solution

by:
NerdsOfTech earned 880 total points
ID: 24266563
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.
0
 
LVL 20

Expert Comment

by:NerdsOfTech
ID: 24266568
SELECT COUNT(*) FROM usermodel GROUP BY modelID
0
 
LVL 20

Expert Comment

by:NerdsOfTech
ID: 24266580
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
0
 
LVL 20

Expert Comment

by:NerdsOfTech
ID: 24266801
You could also JOIN table:model to get model names on top of that...

The possibilities are much, much greater....
0
 
LVL 20

Expert Comment

by:NerdsOfTech
ID: 24266803
Less repeated/manual data = less errors
0
 
LVL 1

Author Closing Comment

by:M-Geek
ID: 31577419
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.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month20 days, 10 hours left to enroll

867 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