SQL Server Query for distinct rows

Question

How do I query for distinct customers? Here's the table I have..

CustID  DATE    PRODUCT
=======================
1       Aug-31  Orange
1       Aug-31  Orange
3       Aug-31  Apple   
1       Sept-24 Apple
4       Sept-25 Orange

This is what I want.

# of New Customers            DATE
========================================
2                            Aug-31 
1                            Sept-25

Thanks!

`SELECT Date, COUNT(DISTINCT CustID) FROM YourTable GROUP BY Date`? — Lamak, May 13 '14 at 21:18
Why no customer on Sept 24 and one on Sept 25 in your desired result? — Joachim Isaksson, May 13 '14 at 21:19
well Sept 24 is a returning customer. So I don't care about repeat customers — JLYK, May 13 '14 at 21:23

score 6 · Accepted Answer · answered May 13 '14 at 21:23

6

This is a bit tricky. You want to count the first date a customer appears and then do the aggregation:

select mindate, count(*) as NumNew
from (select CustId, min(Date) as mindate
      from table t
      group by CustId
     ) c
group by mindate

answered May 13 '14 at 21:23

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered May 13 '14 at 21:27

You could use a simple common table expression to find the first time a user id is used;

WITH cte AS (
  SELECT date, ROW_NUMBER() OVER (PARTITION BY custid ORDER BY date) rn
  FROM customers
)
SELECT COUNT(*)[# of New Customers], date FROM cte
WHERE rn=1
GROUP BY date
ORDER BY date

An SQLfiddle to test with.

SQL Server Query for distinct rows

2 Answers2