CODEWITHSUNDEEP
mysqlsql
Toto
Toto

Reputation: 67

MySQL query to get count of only new users each day

I have a table of call records, I need to know for each day how many people are first time calling every day,

Note: they called the selected day, but never before that day. I managed to have this working.

SELECT * FROM call_history GROUP BY caller_id HAVING SUBSTR(MIN(call_date), 1, 10) = SUBSTR(NOW(), 1, 10)

This query gives me all the people who called today (or any date I put in instead of NOW) but never called before, I need to find a way to get the results for all days in a single query and not ask day by day,

result should be like that:

2013-01-01, 10 new callers
2013-01-01, 15 new callers
2013-01-01, 22 new callers
2013-01-01, 4 new callers

and so on....

Thank you!

Upvotes: 2

Views: 2990

Answers (2)

Steven Moseley
Steven Moseley

Reputation: 16325

I think this will perform best for what you're looking for.

The inner query gets the first call date by caller, and the outer query groups them into callers per call date.

SELECT first_call_date, COUNT(caller_id) AS caller_count
FROM (
    SELECT caller_id, DATE(MIN(call_date)) AS first_call_date
    FROM call_history 
    GROUP BY caller_id
) AS ch
GROUP BY first_call_date

Upvotes: 4

Ankur
Ankur

Reputation: 12774

Try this,

SELECT c1.call_date,
       count(caller_id)
FROM call_history c1
WHERE NOT EXISTS
    (SELECT ID
     FROM call_history c2
     WHERE c2.call_date<c1.call_date and c2.caller_id=c1.caller_id)
GROUP BY c1.call_date

Upvotes: 0

Related Questions