CODEWITHSUNDEEP
sql
CT1
CT1

Reputation: 1

Count the number of initial conacts per day SQL

This is from someone with a low level of SQL knowledge using MS Access

I have some data that looks like this in a table called T1:

Customer    ...........Visit  
c………….      01/01/2001  
b……………      01/01/2001  
b……………      01/01/2001  
a………..      01/01/2001  
b………..  02/01/2001  
a……..       02/01/2001  
d…………       02/01/2001  
e………..      03/01/2001  
d……….       03/01/2001  
c………..      03/01/2001

I've written some SQL to identify the first instance of a customer: 

   SELECT Customer , MIN(Visit) as 'First Contact'  
     FROM T1   
     GROUP BY cust;

This produces a list of the first day customers contact us

Customer......  'First Contact'  
a…...............       01/01/2001  
b………    ....    01/01/2001  
c………......      01/01/2001  
d……..........       02/01/2001  
e……...……        03/01/2001

All fine and dandy but what I need however is a total of first contacts by day i.e. 

First_Contact_Date… NoOfNewContacts  
01/01/2001……... ............    3  
02/01/2001…………........          1  
03/01/2001………............       1

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Basically, you are half-way there. You just need one more aggregation:

SELECT FirstContactDate, COUNT(*)
FROM (SELECT Customer , MIN(Visit) as FirstContactDate
      FROM T1
      GROUP BY cust
     ) t
GROUP BY FirstContactDate
ORDER BY FirstContactDate;

A note on data types: Visit should be stored in the database using a date or related type. This will ensure that the order by works correctly. If you have to store a date as a string, then use the ISO standard YYYY-MM-DD format. Then the order by will work correctly.

Upvotes: 2

Related Questions