CODEWITHSUNDEEP
sqlsql-servert-sql
lypskee
lypskee

Reputation: 342

How to count number of unique values in N columns

How to verify the number of unique values ​​in the columns? For example I have a table:

Shop_1 Shop_2 Shop_3 Shop_4
Adidas Nike Adidas
Reebok Nike Adidas Asics
Ascics Asics Asics Asics
Nike Nike Nike Adidas

For this table, I would like to have an additional column with information on how many unique stores appeared in a given record. The results should be as follows:

  1. First row: 2 (because there was Nike and Adidas)
  2. Second row: 4
  3. Third row: 1 (there were 4 shops but all Asics)
  4. Fourth row: 2
CREATE TABLE shops
(ID INTEGER PRIMARY KEY,
shop1 CHAR(20),
shop2 CHAR(20),
shop3 CHAR(20),
shop4 CHAR(20),
expected_result INT )

INSERT INTO shops VALUES (1, 'Adidas', 'Nike', 'Adidas', null, 2);
INSERT INTO shops VALUES (2, 'Reebok', 'Nike', 'Adidas', 'Asics', 4);
INSERT INTO shops VALUES (3, 'Asics', 'Asics', 'Asics', 'Asics', 1);
INSERT INTO shops VALUES (4, 'Nike', 'Nike', 'Nike', 'Adidas', 2);

Upvotes: 0

Views: 181

Answers (4)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22167

PLease try the following method.

SQL #1

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Shop_1 VARCHAR(20), Shop_2 VARCHAR(20), Shop_3 VARCHAR(20), Shop_4 VARCHAR(20));
INSERT INTO @tbl (Shop_1, Shop_2, Shop_3, Shop_4) VALUES
('Adidas', 'Nike', 'Adidas', NULL),
('Reebok', 'Nike', 'Adidas', 'Asics'),
('Asics', 'Asics', 'Asics', 'Asics'),
('Nike', 'Nike', 'Nike', 'Adidas');
-- DDL and sample data population, end

SELECT * 
    , (
       SELECT Shop_1, Shop_2, Shop_3, Shop_4
       FOR XML PATH(''), TYPE, ROOT('root')
    )
    .value('count(distinct-values(/root/*/text()))','INT') AS [Counter]
FROM @tbl AS p;

SQL #2

It allows to handle a scenario where column list is vary: Shop_1, Shop_2, ..., ShopN.

SELECT * 
    , (
       SELECT *
       FROM @tbl AS c 
       WHERE c.ID = p.ID
       FOR XML PATH(''), TYPE, ROOT('root')
    )
    .value('count(distinct-values(/root/*[local-name()!="ID"]/text()))','INT') AS [UniqueCounter]
FROM @tbl AS p;

Output

+----+--------+--------+--------+--------+---------+
| ID | Shop_1 | Shop_2 | Shop_3 | Shop_4 | Counter |
+----+--------+--------+--------+--------+---------+
|  1 | Adidas | Nike   | Adidas | NULL   |       2 |
|  2 | Reebok | Nike   | Adidas | Asics  |       4 |
|  3 | Asics  | Asics  | Asics  | Asics  |       1 |
|  4 | Nike   | Nike   | Nike   | Adidas |       2 |
+----+--------+--------+--------+--------+---------+

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

A dynamical solution might be by using a catalog table(information_schema.columns) such as

DECLARE @cols1 AS NVARCHAR(MAX), @cols2 AS NVARCHAR(MAX),  @query AS NVARCHAR(MAX)

SET @cols1 = ( SELECT STRING_AGG(QUOTENAME([column_name]),',')
                 FROM information_schema.columns
                WHERE [column_name] != 'ID'
                  AND [table_name]='shops');
               
SET @cols2 = ( SELECT STRING_AGG('COUNT('+QUOTENAME([column_name])+') 
                                   OVER (PARTITION BY ID ORDER BY ID)','+') AS total 
                 FROM information_schema.columns
                WHERE [column_name] != 'ID');
               
SET @query = N'SELECT [ID], '+ @cols1 +',' + @cols2 +  
             N'  FROM [shops]';
--SELECT @query;
EXEC sp_executesql @query;

Demo

Upvotes: 1

Stu
Stu

Reputation: 32579

One way you can do this would be to use cross apply to pivot and then count

select * 
from #t
cross apply (
    select Count (distinct shops) UniqueCount
    from (
        values (shop_1),(shop_2),(shop_3),(shop_4)
    )x(shops)
)a

DB Fiddle

Upvotes: 2

Javi Torre
Javi Torre

Reputation: 824

Just count the distinct values for the columns.

SELECT
    COUNT(DISTINCT shop_1) count1, 
    COUNT(DISTINCT shop_2) count2, 
    COUNT(DISTINCT shop_3) count3, 
    COUNT(DISTINCT shop_4) count4
FROM
    your_table

Upvotes: -1

Related Questions