How to select unique sessions per unique dates with SQL?

I’m struggling with my SQL. I want to select all unique sessions on unique dates from a table. I don’t get the results I want.

Example of table:

session_id | date
87654321   | 2020-05-22 09:10:10
12345678   | 2020-05-23 10:19:50
12345678   | 2020-05-23 10:20:23
87654321   | 2020-05-23 12:00:10

This is my SQL right now. I select all distinct dates from a datetime column. I also count all distinct session_id’s. I group them by date.

SELECT DISTINCT DATE_FORMAT(`date`, '%d-%m-%Y') as 'date', COUNT(DISTINCT `session_id`) as 'count' FROM `logging` GROUP BY 'date'

What I want to see is (with example above):

date       | count
22-05-2020 | 1
23-05-2020 | 2

The result I get with my real table (with 354 sessions on 3 different dates) right now is:

date       | count
21-05-2020 | 200

Edit

Changes ` to ‘.

Answer

The name of the field and the name of the alias is the same (date). Please try to use different name for the alias to avoid confusion in GROUP BY part

Edit

Advertisement

Answer