Get count With Distinct in SQL Server

Question

Select
Count(Distinct iif(t.HasReplyTask = 1, t.CustomerID, Null)) As Reply,
Count(Distinct iif(t.HasOverdueTask = 1, t.CustomerID, Null)) As Overdue,
Count(Distinct t.CustomerID) As Total
From 
Table1 t

If a customer is in Reply, we need to remove that customer in Overdue count, That means if Customer 123 is in both, The Overdue count should be one less. How can I do this?

I am adding some data here, Customer 123 has "HasReplyTask", so, we have to filter that customer from Count in OverDue(even though that customer has one Overdue task without HasReplyTask). 234 is one and Distinct of 456 is one. So, the overdue count should be 2, Above query returns 3

Take one value and minus it from the other? Sample data and expected results will help us help you here. — Thom A
– Thom A ♦, Commented Jan 25, 2022 at 13:55
Yeah, but if there are more, how can we take that? 1 is an example, there can be more Customers overlapped between Reply and Overdue — Chatra
– Chatra, Commented Jan 25, 2022 at 13:57
Why not upload images of code/errors when asking a question?. Data can also be considered code.... — Luuk
– Luuk, Commented Jan 25, 2022 at 14:28
SELECT count(*) FROM (SELECT CustomerId FRoM Table1 GROUP BY CustomerId HAVING MAX(HasReplyTask)=0) x — Luuk
– Luuk, Commented Jan 25, 2022 at 14:30
@Luuk How can I fit this Select statement in above query? Sorry, I am new to SQL — Chatra
– Chatra, Commented Jan 25, 2022 at 15:05

Anton Grig · Accepted Answer · 2022-01-25 16:03:56Z

1

If I've got it right, this can be done using a subquery to get the numbers for each customer, and then get the summary information as follows:

Select Sum(HasReplyTask) As Reply, 
       Sum(HasOverdueTask) As Overdue,
       Count(CustomerID) As Total
From (
Select CustomerID, 
       IIF(Max(Cast(HasReplyTask As TinyInt))<>0, 0, Max(Cast(HasOverdueTask As TinyInt))) As HasOverdueTask,
       Max(Cast(HasReplyTask As TinyInt)) As HasReplyTask
From Table1
Group by CustomerID) As T

I don't know about column data types, so I used cast function to use max function.

db<>fiddle

Reply	Overdue	Total
1	2	3

edited Jan 25, 2022 at 16:03

answered Jan 25, 2022 at 15:05

Anton Grig

1,71910 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DRapp · Accepted Answer · 2022-01-25 15:13:10Z

What would probably be more efficient for you is to pre-aggregate your table by customer ID and have counts per customer. Then your outer query can test for whatever you are really looking for. Something like

select
      sum( case when PQ.ReplyCount > 0 then 1 else 0 end ) UniqReply,
      sum( case when PQ.OverdueCount > 0 then 1 else 0 end ) UniqOverdue,
      sum( case when PQ.OverdueCount - PQ.ReplyCount > 0 then 1 else 0 end ) PendingReplies,
      count(*) as UniqCustomers
   from
      ( select 
              yt.customerid,
              count(*) CustRecs,
              sum( case when yt.HasReplyTask = 1 then 1 else 0 end ) ReplyCount,
              sum( case when yt.HasOverdueTask = 1 then 1 else 0 end ) OverdueCount
           from
              yourTable yt
           group by
              yt.customerid ) PQ

Now to differentiate the count you are REALLY looking for, you might need to do a test against the prequery (PQ) of ReplyCount vs OverdueCount such as... For a single customer ID (thus the pre query), if the OverdueCount is GREATER than the ReplyCount, then it is still considered overdue? So for customer 123, they had 3 overdue, but only 2 replies. You want that counted once? But for customers 234 and 456, the only had overdue entries and NO replies. So, the total where Overdue - Reply > 0 = 3 distinct people.

Is that your ultimate goal?

Collectives™ on Stack Overflow

Get count With Distinct in SQL Server

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related