Getting Count of Distinct Values in Resultset

Question

Hi,

Running SQL Server 2008 R2. DDL and Sample Data are below.

CREATE TABLE [dbo].[Test]

(

[PK] [varchar](50) NULL,

[FK] [varchar](50) NULL,

[ID1] [varchar](50) NULL,

[ID2] [varchar](50) NULL,

[Data] [varchar](50) NULL

)

INSERT INTO [dbo].[Test]

([PK], [FK], [ID1], [Data])

VALUES

('PK1', 'FK1', 'ID21', 'AAA'),

('PK2', 'FK1', 'ID22', 'AAB'),

('PK3', 'FK1', 'ID23', 'AAC'),

('PK4', 'FK2', 'ID21', 'ABA'),

('PK5', 'FK3', 'ID22', 'ABB'),

('PK6', 'FK3', 'ID26', 'XXY'),

('PK6', 'FK3', 'ID26', 'YYZ')

I would like to return a query with all of the above rows and columns but I also want to include a distinct count of FK values. If I implement a COUNT DISTINCT I have to use a GROUP BY clause which leaves me with a value of 1 in the count column for every row. What I would like is the rows returned with a value of 3 in the count column.

Any assistance is greatly appreciated!

Answer 1

To return the overall count of distinct FK values on each row of the result, you can use a subquery:

SELECT 
	  [PK]
	, [FK]
	, [ID1]
	, [Data]
	, (SELECT COUNT(DISTINCT FK) FROM dbo.Test) AS CountDistinctFK
FROM dbo.Test;

Answer 2

Hi @VDT-7677

What I would like is the rows returned with a value of 3 in the count column.

You could use Cross Apply to apply Count() to each row of TEST table.

select *
from Test cross apply(select COUNT(DISTINCT FK)as Count_col from Test)c

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.