How can I get the count of the number of rows for each date in this table?

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.

Solution

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

fiddle