The simplest and best way to get yesterday's date is:
Your query would be:
SELECT url as LINK, count(*) as timesExisted, sum(DateVisited between UNIX_TIMESTAMP(subdate(current_date, 1)) and UNIX_TIMESTAMP(current_date)) as timesVisitedYesterday FROM mytable GROUP BY 1
For the curious, the reason that
sum(condition) gives you the count of rows that satisfy the condition, which would otherwise require a cumbersome and wordy
case statement, is that in mysql boolean values are
1 for true and
0 for false, so summing a condition effectively counts how many times it's true. Using this pattern can neaten up your SQL code.