Select DISTINCT Multiple MySQL Columns

Question

I have a table with more than 30K rows and several columns.

Example:

id | year | make | model | color 1 | 2001 | gm | truck | red 2 | 2004 | gm | truck | green 3 | 2001 | nissan | Max | yellow 4 | 2001 | gm | truck | blue 5 | 2002 | gm | truck | green 6 | 2001 | nissan | Sentra | green 

Since there are many colors for each model and model year, I need to find out how many colors for each car.

Desired Results:

 2001 Nissan Max 5 colors 2001 GM Truck 10 colors 

No need to know which colors are just how many colors.

I tried the following:

 SELECT COUNT(DISTINCT make||model||year) AS number FROM colors LIMIT 10 

Any help would be greatly appreciated

Answer 1

You almost had this:

 SELECT make, model, year, COUNT(DISTINCT color) AS number FROM colors group by make, model, year LIMIT 10