MySQL multiple rows

Question

I am learning databases and I'm hitting an issue and I want someone to explain it to me. I have a table.

car     | car_id | car_type
________________________________
bmw     |   1    | <bmwcartype1>
bmw     |   1    | <bmwcartype2>
bmw     |   1    | <bmwcartype3>
bmw     |   1    | <bmwcartype4>
mercedes|   2    | <mercedescartype1>
mercedes|   2    | <mercedescartype2>
mercedes|   2    | <mercedescartype3>
lexus   |   3    | <lexuscartype1>
lexus   |   3    | <lexuscartype2>
lexus   |   3    | <lexuscartype3>
lexus   |   3    | <lexuscartype4>
lexus   |   3    | <lexuscartype5>

I want to display for example the car and the number of cartypes. I'm not sure how to explain this but on the table above I only managed to get the first row.

SELECT car, COUNT(car) AS number_of_cars FROM cars WHERE car_id = 1

This is displaying the first row like:

car | number_of_cars
bmw |   4

And I want the following rows to be:

car      | number_of_cars
bmw      | 4
mercedes | 3
lexus    | 5

How can I do that?

@GordonLinoff That was exactly what I was looking for, Gordon. Can you post your answer properly so I can mark it as solved and rate you? — MrSilent
– MrSilent, Commented May 14, 2014 at 23:20

Gordon Linoff · Accepted Answer · 2014-05-14 23:26:07Z

1

You have an aggregation query that returns one row. Just as a note, this query works in MySQL but would fail in most databases. The column car is not in a group by. Even though you are choosing only one value, SQL engines don't usually allow this (MySQL is an exception).

The query that you want is:

SELECT car, COUNT(car) AS number_of_cars
FROM cars
group by car;

Note that car appear both in the select and group by. Although you could have car_id in the group by. this is bad form. Try to be sure that the unaggregated columns in the select are always in the group by -- at least until you really understand what you are doing with SQL.

answered May 14, 2014 at 23:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

MySQL multiple rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related