CODEWITHSUNDEEP
mysqlsql
Artur Lunardi Di Fante
Artur Lunardi Di Fante

Reputation: 108

SQL - Group By Two Distinct Values

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

But when I run this query, grouping by date:

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

It return this:

DataCadastro Contagem
2021-01-11 8
2021-01-12 1
2021-01-18 1

I understand that the group by it's returning all registers because I have different dates for the same Id and when I group that, it doesn't matter if the id's are the same. But how can I fix that? How can I tell the query that I want that the group by get only the distinct id's and get the first/last register of date of it?

My desired output would be like:

DataCadastro Contagem
2021-01-11 8
2021-01-18 1

OR

DataCadastro Contagem
2021-01-11 7
2021-01-12 1
2021-01-18 1

Upvotes: 1

Views: 260

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

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

Upvotes: 2

Related Questions