Chris Bolton Chris Bolton - 6 months ago 9
SQL Question

count dates between range

I am trying to count the dates between a range. I have looked here but this is not working for me.

Here is a sample of my table

repo | revision | date | author

test | 1 | 01/01/2011 | chris
test | 2 | 01/01/2011 | chris
test | 3 | 01/02/2011 | chris


So I want to count the dates that are equivalent for a repo... So in this case it would return
2
for
01/01/2011
and
1
for
01/02/2011


Here is the closest I think I have gotten to accomplishing this...

select date, count(*)
from SVNLogEntry
where repo = 'test' and date between '01/01/2011' and '01/01/2017'
group by date;

Answer

If you want the number of distinct dates, then use count(distinct):

select repo, count(distinct date)
from SVNLogEntry
where repo = 'test' and date between '2011-01-01' and '2017-01-01'
group by repo;

To get them per repo, then use group by repo, not group by date.

Comments