Help with SQL Server query

Question

I have table that contain name and date.

I need to count how many names I have in a one day, and make average to all days.

How to do it?

Thanks in advance

Answer 1

I don't have a sql processor to hand but something like

select dateadd(dd,0, datediff(dd,0,[date])), count(name)
from your_table
group by dateadd(dd,0, datediff(dd,0,[date])) 

This should count the names on a particular date. Note dateadd(dd,0, datediff(dd,0,[date])) is there to eliminate the time portion of the date, I am assuming that there may be a time component and that you wanted to count all things on the day.

This should average the names over the day

select dateadd(dd,0, datediff(dd,0,[date])), avg(name)
from your_table
group by dateadd(dd,0, datediff(dd,0,[date])) 

Answer 2

To get daily counts:

SELECT COUNT(name) AS c, date
FROM table
GROUP BY date

For the average:

SELECT AVERAGE(c)
FROM (SELECT COUNT(name) AS c, date
    FROM table
    GROUP BY date)