I have this table:
City. Date
Paris: 2024-04-28
Lyon : 2024-04-28
Lens : 2024-04-28
Paris: 2024-04-29
Lyon: 2024-04-29
London: 2024-04-30
Swindon:2024-04-30
Paris: 2024-05-01
Metz : 2024-05-01
...
I want to know how many cities are present for each date? So something like:
2024-04-28: 3
2024-04-29: 2
2024-04-30: 2
2024-05-01: 2
I have tried this:
select date, count(*) from table group by date;
select count(city), date from table group by date;
select count(distinct city), date from table group by date;
And none of them have worked. I'm getting weird numbers like: 2024-04-28: 50
when I've run these.
Counting the distinct cities per day is that what you are searchung for
CREATE tABLE event (city varchar(10), `Date` date);
INSERT INTO event VALUES
('Paris', '2024-04-28'),
('Lyon', '2024-04-28'),
('Lens', '2024-04-28'),
('Paris', '2024-04-29'),
('Lyon', '2024-04-29'),
('London', '2024-04-30'),
('Swindon', '2024-04-30'),
('Paris', '2024-05-01'),
('Metz', '2024-05-01');
Records: 9 Duplicates: 0 Warnings: 0
SELECT COUNT(DISTINCT city), `Date` FROM event GROUP BY `Date`;
COUNT(DISTINCT city) | Date |
---|---|
3 | 2024-04-28 |
2 | 2024-04-29 |
2 | 2024-04-30 |
2 | 2024-05-01 |