Inner join two tables to order second table

Question

I have two tables, first one keeps ratings for customers: CustId and Point (There may be more than one points for one customer) The second one keeps the information about customers. I want to order top ten customers so I have to average rating for each customer then show top 10 customers from the second table. I tried to use joining the tables but I couldn't. Could you advise me the simplest way to perform this action?

sample data: tb_rating:

Cust_Id Rating  
1        3  
1        5  
2        3  
1        3  
2        1

tb_Customers:

Cust_Id Cust_Name  
1        aaaa  
2        bbbb

@Notulysses There is no working query. I tried to modify [this](http://stackoverflow.com/questions/1572831/avg-of-a-sum-in-one-query) to my case but it didn't work. Sample data is simple: rating table: rating - Cust_Id 4 - 1 5 - 2 3 - 1 3 - 2 Customer Table: Cust_Id Cust_Name 1 - aaa 2 - bbbb — meeteine, Jun 16 '14 at 03:55

NeedAnswers · Answer 1 · 2014-06-16T04:15:59.797

0

A straight approach, using innerjoin or where clause in this case does not make any much differences :

select a.cust_ID, a.Cust_Name, b.Rating
from  Customer a,  
    (
    select top 10 cust_ID, avg(Rating) as Point from rating 
    group by cust_ID order by avg(Rating) desc 
    ) b where a.cust_ID = b.cust_ID 
order by a.cust_name

edited Jun 16 '14 at 04:15

answered Jun 16 '14 at 04:06

NeedAnswers

1,411
3
19
43

it didn't work. everything seem ok but it doent return any row. – meeteine Jun 16 '14 at 04:50

Thomas Padron-McCarthy · Accepted Answer · 2014-06-16T04:45:38.673

select tb_customers.cust_id, tb_customers.cust_name, avg(rating) as average
from tb_customers join tb_rating on tb_customers.cust_id = tb_rating.cust_id
group by tb_customers.cust_id, tb_customers.cust_name
order by average desc;

You haven't said which DBMS you are using, and how to get the first ten rows in the result depends on that. If it is SQL Server, just add "top 10" after "select". If it is MySQL, add "limit 0, 10" at the end of the query. Other systems may require other ways of expressing it.

One more detail: If the column "rating" has type integer, you may (depending again on which system you are using) have to replace "avg(rating)" with "avg(1.0 * rating)" if you want decimals in your averages.

To write a query like this, it is often easiest to think step by step, and try each step. First, join the two tables, so you have all the data you need in one big table:

select *
from tb_customers join tb_rating on tb_customers.cust_id = tb_rating.cust_id;

Try to run it with your data, so you see that the result is what you would expect. Then, calculate the average for each customer, using "avg" and "group by":

select tb_customers.cust_id, tb_customers.cust_name, avg(rating) as average
from tb_customers join tb_rating on tb_customers.cust_id = tb_rating.cust_id
group by tb_customers.cust_id, tb_customers.cust_name;

Run that too. Then add the "order by" as shown above, and finally how to get the first ten rows.

it is SQL server. what is point? you mean tb_rating.Rating? it worked, almost. There is one point: if I add tb_customers.cust_name in the query it doesnt work. I want to show the names as output not id. select tb_customers.cust_Name,tb_customers.cust_id,AVG(tb_rating.rating) as average FROM tb_customers JOIN tb_rating ON tb_customers.cust_id=tb_rating.cust_id GROUP BY tb_customers.cust_id ORDER BY average DESC What is wrong with this query? — meeteine, Jun 16 '14 at 04:42
@meeteine: Yes, I mean "rating". Fixed it now. (But to my defense, in the first line of your questions it says "point" not "rating".) And I've added tb_customers.cust_name in the query. — Thomas Padron-McCarthy, Jun 16 '14 at 04:49
I just asked to clear, and you're right; my question is not clear enough :) it is perfect now. thanks :) — meeteine, Jun 16 '14 at 05:00

Inner join two tables to order second table

2 Answers2