How to get distinct values and count its unrepeatable values? SQL

Question

I have a table SoldCars.

Id_Car                               Id_Color                         time
8                                      2                          2015-02-11
8                                      4                          2015-03-11
8                                      2                          2015-04-11
5                                      2                          2015-05-11
8                                      3                          2015-08-11

I would like to query to get the following rowset:

Id_car          CountColors
8                   3
5                   1

Where Id_car is unique Id_Car and CountColors is count of distinct quantity of colors.

I've tried a lot of SQL queries, but the result is far from good.

For Example:

Select dictinct Id_Car, count(Id_Color) as CountColors from SoldCars group by Id_car

Select Id_Car, count(DISTINCT Id_Color) as CountColors from SoldCars group by Id_car — Tab Alleman, Dec 21 '15 at 19:46
Appreciate the offer, but since a duplicate was identified, please just delete or leave it closed. Thanks! — Tab Alleman, Dec 21 '15 at 20:51

score 6 · Accepted Answer · answered Dec 21 '15 at 19:47

6

SELECT Id_car, count(DISTINCT Id_Color) AS CountColors FROM SoldCars GROUP BY Id_car

This should do the job :)

answered Dec 21 '15 at 19:47

Tobias Baumeister

2,107
3
21
36

How to get distinct values and count its unrepeatable values? SQL

1 Answers1