mysql - SQL - Group By Two Distinct Values

Question

I have this sample data:

CREATE TABLE teste
(`leadId` INT, `dates` datetime)
;

INSERT INTO teste
(`leadId`, `dates`)
VALUES
(1, '2021-01-18 13:03:36'),
(2, '2021-01-11 19:40:47'),
(3, '2021-01-11 13:28:54'),
(4, '2021-01-11 19:44:16'),
(5, '2021-01-11 13:28:24'),
(6, '2021-01-11 13:29:28'),
(7, '2021-01-11 18:17:26'),
(8, '2021-01-11 19:40:22'),
(9, '2021-01-11 18:35:32'),
(9, '2021-01-12 17:49:03')
;

I want to perform a query that return the distinct count of leadId grouped by day. There are cases that the leadId it's duplicated for some misuse, and I want to filter that.

When I run this query:

SELECT date(teste.dates) AS DataCadastro,
COUNT(DISTINCT teste.leadId) AS Contagem
FROM teste;

It returns 9, which are the distinct leadId's.

DataCadastro	Contagem
2021-01-18	9

question from:https://stackoverflow.com/questions/65944641/sql-group-by-two-distinct-values

Answer 1

You could count the earliest day for each id:

select min_date, count(*)
from (select id, min(date) as min_date
      from t
      group by id
     ) t
group by min_date