Select DISTINCT multiple columns MySQL

Question

I have a table with over 30K rows and multiple 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 color for each make model and year, I need to find out how many color for each vehicle.

Desired Results:

2001 Nissan Max 5 colors
2001 GM Truck 10 colors

No need to know what colors just how many colors.

I tried the following:

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

Any help would be much appreciated

possible duplicate of [MySQL SELECT DISTINCT multiple columns](http://stackoverflow.com/questions/12188027/mysql-select-distinct-multiple-columns) — Hein Andre Grønnestad, Jul 25 '13 at 08:10

score 5 · Accepted Answer · edited Jul 06 '21 at 23:39

5

You almost had it:

SELECT make,
       model,
       year,
       COUNT(DISTINCT color) AS number 
FROM colors
GROUP BY make, model, year
LIMIT 10;

edited Jul 06 '21 at 23:39

informatik01

16,038
10
74
104

answered Jul 25 '13 at 08:10

Orangepill

24,500
3
42
63

Select DISTINCT multiple columns MySQL

1 Answers1