CODEWITHSUNDEEP
mysql
Bibhu Biswal
Bibhu Biswal

Reputation: 181

count unique values in a group, and exclude already counted rows in mysql

I would like to count all unique visitors group by day.But i want to exclude the visitor if he has already visited any previous day.My sample table is as below

mysql> select * from visitor;
+-------+------------+--------------+
| user  | visit_date | No Of Visits |
+-------+------------+--------------+
| user1 | 20150101   | 10           |
| user2 | 20150102   | 1            |
| user3 | 20150101   | 1            |
| user1 | 20150102   | 2            |
+-------+------------+--------------+

My requirement is to get a distinct count of user group by visit date but to exclude already visited user.

20150101 --> User1 and user 3 visited

20150102 --> User 2 visited (Exclude user1 as he has already visited)

+----------------------+------------+
| count(distinct user) | visit_date |
+----------------------+------------+
|                    2 | 20150101   |
|                    1 | 20150102   |
+----------------------+------------+

Upvotes: 0

Views: 142

Answers (2)

shawnt00
shawnt00

Reputation: 17915

select visit_date, count(distinct user)
from visitors as v1
where not exists (
    select 1
    from visitors as v2
    where
            v2.user = v1.user
        and v2.visit_date = date_sub(v1.visit_date, interval 1 day)
)
group by visit_date

or

select v1.visit_date, count(distinct v1.user)
from
    visitors as v1
    left outer join visitors as v2
        on      v2.user = v1.user
            and v2.visit_date = date_sub(v1.visit_date, interval 1 day)
where v2.user is null
group by v1.visit_date

Not really sure if DISTINCT is actually necessary in the aggregate.

Upvotes: 0

Bibhu Biswal
Bibhu Biswal

Reputation: 181

Solved

select count(*),hr from (select user, min(visit_date)as hr from visitor v group by user) as data group by hr

Upvotes: 0

Related Questions