SQL Server : count how many times one

Question

I am learning SQL and I am stuck with a certain question for a while. I have a huge data set looking like:

id  v1
1   3
2   3
3   -
4   5
5   3
6   5
7   3

I need to count how many times each id is in v1. The output i seek is:

id  count
1   0
2   0
3   4
4   0
5   2
6   0
7   0

Have been looking for an answer on many forums. The problem is that there are a lot of ids so that I can`t search by number "1" and so on. If I use something like id=v1 i get how many times a row has equal values in these columns. Looking for some help. Please.

Answer 1

Try this:

SELECT t1.id, COUNT(t2.v1)
FROM mytable AS t1
LEFT JOIN mytable AS t2 ON t1.id = t2.v1
GROUP BY t1.id
ORDER BY t1.id 

Demo here