CODEWITHSUNDEEP
mysqlgroup-by
user1049508
user1049508

Reputation: 21

Get information from two tables

I have two tables that contain the following information

models

id      manufactor 
1       Mercedes
2       Ford
3       Volvo

orders
car_id customer_id
1      4
1      22
2      54
1      95

I want a query that will give in one row manufactor name and how many of each model are sold.

Here is what I have tried:

SELECT
  m.make
FROM models m
LEFT JOIN(
    SELECT COUNT(*)FROM orders
) o on o.car_id = m.id

Upvotes: 0

Views: 89

Answers (3)

J Bourne
J Bourne

Reputation: 1439

SELECT m.make, sum(o.car_id) as total_sold FROM models as m 
LEFT JOIN SELECT  * from orders as o on o.car_id=m.id group 
by o.car_id order by o.car_id

Upvotes: 0

Nalum
Nalum

Reputation: 4213

Try this:

SELECT m.manufactor, COUNT(o.car_id)
FROM models m
LEFT JOIN orders o ON m.id = o.car_id
GROUP BY o.car_id

Upvotes: 2

Vinayak Mahadevan
Vinayak Mahadevan

Reputation: 191

you can change the query as follows

SELECT
m.make,o.totcount
FROM models m
LEFT JOIN(
SELECT COUNT(*) as totcount FROM orders group by car_id
) o on m.id = o.car_id

Upvotes: 0

Related Questions