getting distinct values in groups starting from one particular date mysql

Question

My scenario is like below.

id     email                    created_at
1     test1@gmail.com           2018-01-01
2     test1@gmail.com           2018-01-01
3     test2@gmail.com           2018-01-01
4     test3@gmail.com           2018-01-01
5     test4@gmail.com           2018-01-02
6     test1@gmail.com           2018-01-02
7     test1@gmail.com           2018-01-02
8     test5@gmail.com           2018-01-03
9     test4@gmail.com           2018-01-03

I want to get distinct email values on each day that are not exist in previous dates. If I group by date I will get results like below.

select count(distinct email) as count, created_at from test_table group by created_at

count     created_at
3         2018-01-01
2         2018-01-02
2         2018-01-03

But I need result like below

count     created_at
3         2018-01-01
1         2018-01-02
1         2018-01-03

Please provide solution to above.

If less typing is your thing, how about http://sqlfiddle.com/#!9/5a6526/11 — Strawberry, Jan 27 '18 at 08:07

Alexis.Rolland · Answer 1 · 2018-01-27T07:42:23.170

0

I would suggest you replicate rank() over() logic describes in this post: Rank() over Partition by in mysql

I have tested this on my side and it worked. See fiddle here: http://sqlfiddle.com/#!9/5a6526/10/0

CREATE TABLE `table` (
  `email` varchar(100),
  `created_at` varchar(10)
);

INSERT INTO `table` (`email`, `created_at`) VALUES
  ('test1@gmail.com', '2018-01-01'),
  ('test1@gmail.com', '2018-01-01'),
  ('test2@gmail.com', '2018-01-01'),
  ('test3@gmail.com', '2018-01-01'),
  ('test4@gmail.com', '2018-01-02'),
  ('test1@gmail.com', '2018-01-02'),
  ('test1@gmail.com', '2018-01-02'),
  ('test5@gmail.com', '2018-01-03'),
  ('test4@gmail.com', '2018-01-03')
;

select count(distinct a.email),
a.created_at
from (
    select email,
    created_at,
    case
        when email=@last
        then @curRank:=@curRank+1
        else @curRank:=0
    end as rank,
    @last:=email
    from `table`,
    (select @curRank:=0, @last:='') a
    order by email, created_at
) a
where a.rank=0
group by a.created_at

edited Jan 27 '18 at 07:42

answered Jan 27 '18 at 06:07

Alexis.Rolland

5,724
6
50
77

I haven't tested it because I don't have a mySQL database at hand at the moment but yes it should work out of the box. I'll update my answer if it does not work. – Alexis.Rolland Jan 27 '18 at 06:15
I confirm it does not work as is. The `rank()` function does not exist in MySQL – Alexis.Rolland Jan 27 '18 at 06:28
@SatyanarayanaSwamy I've updated my answer. Let me know if it works? – Alexis.Rolland Jan 27 '18 at 06:40
still iam getting the same result it is not eliminating duplicates from previuos dates – Satyanarayana swamy Jan 27 '18 at 06:45
@Satyanarayanaswamy, I've updated the query above, it worked on my side. Can you try it? – Alexis.Rolland Jan 27 '18 at 07:13
@alexis it is giving error on my side. can you please create a sql fiddle for it.thanks. – Satyanarayana swamy Jan 27 '18 at 07:18
@Satyanarayanaswamy what about now? (cf. updates above) – Alexis.Rolland Jan 27 '18 at 07:36
@Satyanarayanaswamy please consider marking this question as solved if it solved your issue. Thanks – Alexis.Rolland Jan 29 '18 at 08:24

getting distinct values in groups starting from one particular date mysql

1 Answers1