从UserID中获取计数

Question

我需要一种获得与每个ek关联的唯一计数的userid的方法。

我想要的输出是：

74123    3

但是，我似乎无法准确地理解不同计数的语法。我只能让它返回总数，这不是我想要的。

DDL

    CREATE TABLE [dbo].[___________Purple](
    [userid] [int] NULL,
    [ek] [char](100) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Black                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Black                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Black                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Black                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Black                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Black                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Green                                                                                               ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Pink                                                                                                ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Pink                                                                                                ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Pink                                                                                                ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Pink                                                                                                ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Pink                                                                                                ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Pink                                                                                                ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Pink                                                                                                ')
GO
INSERT [dbo].[___________Purple] ([userid], [ek]) VALUES (71423, N'Pink                                                                                                ')

Answer 1

试试这个：

SELECT userid, COUNT(DISTINCT ek) AS total_ek
FROM dbo.[___________Purple]
GROUP BY userid;

结果：

对于像我这样对这个问题是如何找到3种不同的ek的人来说，一定要在他的冗长的DDL查询中向下滚动。