CODEWITHSUNDEEP
sqlsql-server
JLYK
JLYK

Reputation: 391

SQL Server Query for distinct rows

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!

Upvotes: 3

Views: 98

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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

Upvotes: 6

Related Questions