SQL Server – How to Get Count from UserID

sql serversql-server-2008-r2t-sql

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

Best Answer

Try this:

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

The results:

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.

Related Solutions

SQL Server Hierarchical Order – Parent-Child Tree Hierarchical ORDER BY

OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID], 
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID], 
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],  
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID], 
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];

Sql-server – Why would call to scalar function inside a Table Value Function be slower than outside the TVF

Scalar functions are called once-per-row, when called as part of a query.

Consider the following example.

Create a new, blank database for our tests:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'mv')
BEGIN
    ALTER DATABASE mv SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE mv;
END
GO
CREATE DATABASE mv;
GO

Create a table, a multi-statement function, and a table-valued-function:

USE mv;
GO
CREATE TABLE dbo.t
(
    t_id int NOT NULL
        CONSTRAINT PK_t
        PRIMARY KEY CLUSTERED
);
GO

CREATE FUNCTION dbo.t_func
(
    @t_id int
)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @r bit;
    IF EXISTS (SELECT 1 FROM dbo.t WHERE t.t_id = @t_id)
        SET @r = 1
    ELSE
        SET @r = 0;
    RETURN @r;
END
GO

CREATE FUNCTION dbo.t_tvf
(
    @min_t_id int
    , @max_t_id int
)
RETURNS TABLE 
WITH SCHEMABINDING
AS
RETURN (
    SELECT t_id = t.t_id
        , e = dbo.t_func(dbo.t.t_id)
    FROM dbo.t
    WHERE t.t_id >= @min_t_id
        AND t.t_id <= @max_t_id
);
GO

Insert some sample data into the table:

INSERT INTO dbo.t (t_id)
SELECT ROW_NUMBER() OVER (ORDER BY c.id, c.colid)
FROM sys.syscolumns c;
GO

Create a table to store function execution stats, and populate it with a start-row showing execution counts for the multi-statement-function, t_func:

CREATE TABLE dbo.function_stats
(
    run_num int NOT NULL
    , object_name sysname NOT NULL
    , execution_count int NULL 
    , CONSTRAINT PK_function_stats
        PRIMARY KEY CLUSTERED (run_num, object_name)
);
GO
INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
SELECT 1
    , o.name
    , COALESCE(fs.execution_count, 0)
FROM sys.objects o 
    LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
WHERE o.name = 't_func';
GO

Run a query against the TVF:
```
SELECT t.*
FROM dbo.t_tvf(1, 2) t;
GO
```

Capture the execution stats now:

INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
SELECT 2
    , o.name
    , COALESCE(fs.execution_count, 0)
FROM sys.objects o 
    LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
WHERE o.name = 't_func';

The function stats results:

SELECT *
FROM dbo.function_stats fs
ORDER BY fs.run_num
    , fs.object_name;

╔═════════╦═════════════╦═════════════════╗
║ run_num ║ object_name ║ execution_count ║
╠═════════╬═════════════╬═════════════════╣
║       1 ║ t_func      ║               0 ║
║       2 ║ t_func      ║               2 ║
╚═════════╩═════════════╩═════════════════╝

As you can see, the multi-statement-function has execute twice, once per row for the source table accessed by the TVF.

I expect the mutli-statement-function is being called many, many times by the TVF, giving the impression that it is running slowly, whereas in fact it is simply being called many times.

Best Answer

Related Solutions

SQL Server Hierarchical Order – Parent-Child Tree Hierarchical ORDER BY

Sql-server – Why would call to scalar function inside a Table Value Function be slower than outside the TVF

Related Question