Count of Distinct Values

Question

Post reply

Count of Distinct Values

jagjitsingh

Ten Centuries

Points: 1072
More actions
April 6, 2025 at 2:49 am

#4573880
Hi
If value is 21000030 it should return 4 not 5 . In first record 21000030 exists 2 times
21000032 then it should return 3 not 4.
USE [Live]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OCRD](
[CardCode] [nvarchar](15) NOT NULL,
[Emp_1] [nvarchar](10) NULL,
[Emp_2] [nvarchar](10) NULL,
[Emp_3] [nvarchar](10) NULL,
CONSTRAINT [OCRD_PRIMARY] PRIMARY KEY CLUSTERED
(
[CardCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C01',N'21000030', N'21000030', NULL)
INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C02',N'21000032', N'21000030', NULL)
INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C03', N'21000038', N'21000032', NULL)
INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C04', N'21000040', N'21000033', N'21000030')
INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C05', N'21000030', N'21000032', N'21000032')
Thanks
- This topic was modified 2 months, 1 week ago by jagjitsingh.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80342 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Collins SSCrazy Points: 2214 More actions · Answer 2

One way to unpivot unique values (of columns [Emp_1], [emp_2], [Emp_3]) could be to CROSS APPLY their UNOIN. The syntax might appear to be unusual because the subquery doesn't contain any FROM clause(s). In this case the 3 column values are available on the same row and the table contains a primary key so FROM is not necessary

select unq_unpvt.Emp, count(*) unq_count
from #OCRD o
     cross apply (select [Emp_1] where [Emp_1] is not null
                  union
                  select [Emp_2] where [Emp_2] is not null
                  union
                  select [Emp_3] where [Emp_3] is not null) unq_unpvt(Emp)
group by unq_unpvt.Emp
order by unq_unpvt.Emp;

Equivalently the primary key could be used to locate the rows (to CROSS APPLY) but it's not necessary (and probably less efficient)

select unq_unpvt.Emp, count(*) unq_count
from #OCRD o
     cross apply (select [Emp_1] from #OCRD o1 where o1.CardCode=o.CardCode and [Emp_1] is not null
                  union
                  select [Emp_2] from #OCRD o2 where o2.CardCode=o.CardCode and [Emp_2] is not null
                  union
                  select [Emp_3] from #OCRD o3 where o3.CardCode=o.CardCode and [Emp_3] is not null) unq_unpvt(Emp)
group by unq_unpvt.Emp
order by unq_unpvt.Emp;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können