SQL server, how to get a number of distinct items

Question

I am using SQL server,

  id          3     | 4    | 5 | 6
  items       1 2 3 | 2 3 5| 6 | 1 2 5
    -------------------------
  # of items      3 | 4    | 5 | 5

so, each id has items (ex, 3 has 3 items - 1,2,3) and for each item, I'd like to get the number of distinct items accrued.

so, 3 has 3 distinct items - 1, 2, 3
4 has 4 distinct items - 1, 2, 3, 5
5 has 5 distinc items - 1, 2, 3, 5, 6
6 has 5 distinct items - 1, 2, 3, 5, 6

I can do this by running, 1 through 2, 1 though 3, 1 through 5 and 1 through 6 by doing count(distinct items) But I want to automate this process and get the same results in one run.

The idea is to create a temp table and put an item in it while checking if the item is already in the temp table and print number of distinct items for each id.

What have you tried (eg. the query your using to get the number of items by id). — Richard, Oct 17 '16 at 10:32

score 2 · Accepted Answer · answered Oct 17 '16 at 10:28

2

CREATE TABLE TEST
(
    id int, items int
)

INSERT INTO TEST
VALUES
(3, 1),
(3, 2),
(3, 3),
(4, 2),
(4, 3),
(4, 5),
(5, 6),
(6, 1),
(6, 2),
(6, 5)

SELECT B.id, COUNT(DISTINCT(A.ITEMS)) AS itemCount
FROM TEST A
INNER JOIN TEST B ON A.id <= B.id
GROUP BY B.ID

DROP TABLE TEST

Output: 
id  itemCount
3   3
4   4
5   5
6   5

answered Oct 17 '16 at 10:28

Esty

1,882
3
17
36

This is want I would have done too. I might I inverted A and B for better readability but this is only for personal preference. `SELECT A.id , COUNT(DISTINCT (B.items)) AS itemCount FROM TEST A INNER JOIN TEST B ON B.id <= A.id GROUP BY A.id;` – Kilren Oct 17 '16 at 11:12
ha ha. Glad that you acknowledged.:) – Esty Oct 17 '16 at 11:19
I don't have permission to create a table in the database I use. I can only create temp tables. I guess it doesn't really matter? – Nayana Oct 18 '16 at 02:31
what if the IDs weren't consecutive numbers? – Nayana Oct 18 '16 at 02:37
I have created the table for my testing. You will use your existing table. If you don't have any then you could also use temp table, temp variables or common table expression. And yeah if the IDs' weren't consecutive number it will work fine because of joining condition `A.id <= B.id` and `group by`. Please do a test by yourself. If you don't have permission to create table then copy my code and replace the table with table variable. – Esty Oct 18 '16 at 02:58

score 1 · Answer 2 · edited May 23 '17 at 10:34

Assuming your data in below format:

Declare @table table
(
id int,
items varchar(10)
)

insert into @table values (3, '1 2 3');
insert into @table values (4, '2 3 5');
insert into @table values (5, '6');
insert into @table values (6, '1 2 5');

with cte as
(
Select id, b.Item
from @table a
cross apply [dbo].[Split] (items, ' ') b
)

Select y.id, count(distinct(x.Item)) AS [# of items]
from cte x
join cte y on x.id <= y.id
group by y.id

Use the table valued function [dbo].[Split] from LINK.

score 0 · Answer 3 · answered Oct 17 '16 at 11:20

You can as the below:

DECLARE @Tbl TABLE (Id VARCHAR(10), Column3 VARCHAR(100), Column4 VARCHAR(100), Column5 VARCHAR(100), Column6 VARCHAR(100))
INSERT @Tbl
VALUES 
('items', '1 2 3', '2 3 5', '6', '1 2 5')   

;WITH CTE1
AS
(
    SELECT T.Id, T.Column3 AS ColumnId, CAST('<X>' + REPLACE(T.Column3,' ','</X><X>') + '</X>' as XML) AS FilterColumn FROM  @Tbl T UNION ALL
    SELECT T.Id, T.Column4 AS ColumnId, CAST('<X>' + REPLACE(T.Column4,' ','</X><X>') + '</X>' as XML) AS FilterColumn FROM  @Tbl T UNION ALL
    SELECT T.Id, T.Column5 AS ColumnId, CAST('<X>' + REPLACE(T.Column5,' ','</X><X>') + '</X>' as XML) AS FilterColumn FROM  @Tbl T UNION ALL
    SELECT T.Id, T.Column6 AS ColumnId, CAST('<X>' + REPLACE(T.Column6,' ','</X><X>') + '</X>' as XML) AS FilterColumn FROM  @Tbl T 
), CTE2
AS 
(
    SELECT 
        A.*,
        B.SplitData
    FROM
        CTE1 A CROSS APPLY
        (SELECT fdata.D.value('.','varchar(50)') AS SplitData FROM A.FilterColumn.nodes('X') as fdata(D)) B
)

SELECT
    T.Id ,
    (SELECT COUNT(DISTINCT C.SplitData) FROM CTE2 C WHERE C.Id = T.Id AND C.ColumnId IN (T.Column3)) Column3OfDistinct,
    (SELECT COUNT(DISTINCT C.SplitData) FROM CTE2 C WHERE C.Id = T.Id AND C.ColumnId IN (T.Column3, T.Column4)) Column4OfDistinct,
    (SELECT COUNT(DISTINCT C.SplitData) FROM CTE2 C WHERE C.Id = T.Id AND C.ColumnId IN (T.Column3, T.Column4, T.Column5)) Column5OfDistinct,
    (SELECT COUNT(DISTINCT C.SplitData) FROM CTE2 C WHERE C.Id = T.Id AND C.ColumnId IN (T.Column3, T.Column4, T.Column5, T.Column6)) Column6OfDistinct    
FROM
    @Tbl T

Result:

Id         Column3OfDistinct Column4OfDistinct Column5OfDistinct Column6OfDistinct
---------- ----------------- ----------------- ----------------- -----------------
items      3                 4                 5                 5

score -1 · Answer 4 · answered Oct 17 '16 at 10:26

-1

This should help you:

    select
        id,
        count(items)
    from table_name
    group by id

answered Oct 17 '16 at 10:26

user3426496

125
2
7

SQL server, how to get a number of distinct items

4 Answers4