我有一个超过30K行和多列的表。
示例:
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
由于每个品牌的车型和年份都有很多颜色,我需要了解每辆车的颜色。
期望结果:
2001 Nissan Max 5 colors
2001 GM Truck 10 colors
不需要知道什么颜色,只需要知道有多少种颜色。
我尝试了以下方法:
SELECT COUNT(DISTINCT make||model||year) AS number FROM colors LIMIT 10
任何帮助都将不胜感激
你几乎做到了:
SELECT make,
model,
year,
COUNT(DISTINCT color) AS number
FROM colors
GROUP BY make, model, year
LIMIT 10;