CODEWITHSUNDEEP
mysqlsql
daniel93
daniel93

Reputation: 301

SQL group by get another table value by id

I have 2 tables these following tables:

users
id
name
car
cars
id
name

let's say I have these users

id: 1, name: Michael, car: 1

id: 2, name: Danny, car: 2

id: 3, name: Jennifer, car: 1

and these cars

id: 1, name: Audi,

id: 2, name BMW

I am trying to get how many each type of car the users have, so the result should be like:

cars cars count
Audi 2
BMW 1

I am running the following command

SELECT u.car, c.name, COUNT('car count') FROM users u INNER JOIN cars c ON c.id = u.car GROUP BY c.id

which gives the following result

cars name cars count
1 Audi 2
2 BMW 1

How can I disregard the name column but to display the name inside the cars column (like I mentioned above)?

Upvotes: 0

Views: 900

Answers (2)

Andrew
Andrew

Reputation: 1833

Try this:

SELECT 
    c.name as 'car', 
    COUNT('car count') 
FROM 
    users u 
INNER JOIN 
    cars c ON c.id = u.car 
GROUP BY 
    c.id

Upvotes: 1

YHTAN
YHTAN

Reputation: 686

try this.

SELECT c.name as cars, COUNT('car count') 
FROM users u 
INNER JOIN cars c ON c.id = u.car 
GROUP BY c.id

Upvotes: 1

Related Questions