How Can We Count New Distinct Values group by date in SQL

Question

I need to a distinct number of values in a column and group it by date, The real problem is the count should not include if it already occurred in a previous result.

Eg: Consider the table tblcust

 Date        Customers
 March 1     Mike
 March 1     Yusuf
 March 1     John
 March 2     Ajay
 March 2     Mike
 March 2     Anna

The result should be

Date       Customer_count
March 1       3
March 2       2

If I use

select date,count(distinct(customer)) as customer_count
group by date

The Result I am getting is

Date      customer_count    
March 1   3
March 2   3

The customer, Mike has been visited twice, It should not be counted as a new customer.

As I can see your query is correct you need to re-check with the given data as in your question. — Suraj Kumar, Mar 21 '20 at 06:00
I need to count only unique customer in the list, Here the mike has happened twice , so no need to count him in the date march 2 — murshid omr, Mar 21 '20 at 06:10
Check my updated answer now it will work as you are expecting. — Suraj Kumar, Mar 21 '20 at 06:17

Suraj Kumar · Accepted Answer · 2020-03-21T06:21:08.940

You can try and achieve this using SQL Server ROW_NUMBER Function .

create table tblCust (DtDate varchar(20), Customers Varchar(20))
insert into tblCust Values
('March 1', 'Mike'),
('March 1', 'Yusuf'),
('March 1', 'John'),
('March 2', 'Ajay'),
('March 2', 'Mike'),
('March 2', 'Anna')

 Select dtDate
     , Customers
     , ROW_NUMBER() OVER (PARTITION BY Customers ORDER BY DtDate) as SrNo
from tblCust
order by DtDate, SrNo

select Dtdate,
    count(distinct(customers)) as customer_count
from(Select dtDate
     , Customers
     , ROW_NUMBER() OVER (PARTITION BY Customers ORDER BY DtDate) as SrNo
from tblCust
)a where SrNo = 1
group by Dtdate

Here is the live db<>fiddle demo

How Can We Count New Distinct Values group by date in SQL

1 Answers1