How to exclude duplicates when counting?

Question

Say I have data like below:

id, dateVisited
1   3/4/2019  --Monday
1   3/4/2019  --Monday
1   3/5/2019  --Tuesday
2   3/4/3019  --Monday
2   3/5/2019  --Tuesday
2   3/7/2019  --Thursday
3   3/7/2019  --Thursday
4   3/5/2019  --Tuesday
5   3/8/2019  --Friday

How would I get the count of visits per day but only counting a specific id at most 1 time per given day. So for the data above it should return:

day, visits
2    2     --show 2 here instead of 3 because ID 1 visited twice on 3/4
3    2
5    2
6    1

I can get the raw totals by doing:

SELECT COUNT(*) FROM myTable group by DATENAME(dw,dateVisited)

but I can't quite figure out how to exclude duplicates.

Answer 1

I think you want count(distinct):

SELECT DATENAME(dw,dateVisited), COUNT(DISTINCT id)
FROM myTable 
GROUP BY DATENAME(dw, dateVisited)