1

I need a way of getting a unique count of ek that is associated with each userid

My desired output is:

74123    3

But I can not seem to get the syntax for the Distinct count accurate. I can only get it to return the TOTAL count which is not what I am after.

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                                                                                                ')
6

Try this:

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

The results:

enter image description here

And for folks like me who were confused about how the question found 3 different ek's - make sure to scroll down in his epic long DDL query.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.