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 — Hein Andre Grønnestad
– Hein Andre Grønnestad, Commented Jul 25, 2013 at 8:10

informatik01 · Accepted Answer · 2021-07-06 23:39:09Z

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 6, 2021 at 23:39

informatik01

16.5k11 gold badges82 silver badges112 bronze badges

answered Jul 25, 2013 at 8:10

Orangepill

24.7k3 gold badges44 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

Select DISTINCT multiple columns MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related