MySQL relation between tables

Question

Hey so I have a question. I have two tables. table1 and table2. table1:

id | car_numbers |  type  |      model   | type_id
1        3           bmw       <model1>       1
2        5           bmw       <model2>       1
3        2         mercedes    <model1>       2
4        4         mercedes    <model2>       2
5        1         chevrolet   <model1>       3

table2:

id | price | type_id
1     100       1
2     200       1
3     300       2 
4     400       2 
5     500       3

What I want, is to display the 'type', the 'car_numbers' and the average price of each car type. Basically the result of what I want between those two tables is:

  type    | car_numbers | average_price
  bmw            8             150
mercedes         6             350
chevrolet        1             500

How can I do that? I know I have to relate to the type_id that's common in both tables but how can I do that?

This is a basic join query with a group by. You should know these basic constructs in MySQL if you are using the database. — Gordon Linoff
– Gordon Linoff, Commented May 16, 2014 at 22:03

Barmar · Accepted Answer · 2014-05-16 22:16:28Z

1

SELECT type, car_numbers, AVG(price) AS average_price
FROM (SELECT type_id, type, SUM(car_numbers) AS car_numbers
      FROM table1
      GROUP BY type_id) AS t1
JOIN table2 AS t2 ON t1.type_id = t2.type_id
GROUP BY type

DEMO

answered May 16, 2014 at 22:16

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tiago Goddard · Accepted Answer · 2014-05-17 00:38:41Z

1

I think it might be this you are looking for

SELECT t.name, sum(Distinct o.car_number) car ,         avg(w.price) price FROM
TYPE t
INNER JOIN tone AS o ON o.type_id = t.id
INNER JOIN ttwo AS w ON w.type_id = t.id
GROUP BY t.name

http://sqlfiddle.com/#!2/1937a/51

answered May 17, 2014 at 0:38

Tiago Goddard

1496 bronze badges

Comments

Justin Samuel · Accepted Answer · 2014-05-16 22:03:59Z

0

Is this what you are looking for, usage of Group By and Avg:

 SELECT type, car_numbers, avg(price) AS average_price
    FROM table1 T1
    INNER JOIN table2 T2
    ON T1.type_id = T2.type_id
    GROUP BY type, car_numbers

answered May 16, 2014 at 22:03

Justin Samuel

1,0834 gold badges17 silver badges31 bronze badges

1 Comment

PM 77-1 Over a year ago

GROUP BY type, car_numbers ??? Why would you want to group by car_numbers???

Collectives™ on Stack Overflow

MySQL relation between tables

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related