Query Count Per Day

[EnergyTed]

Hi

I am trying to provide stats that show a count per day, lets call them fish!

Table 1 holds the date effective from field (so everytime a change of ownership occurs a new row is added with a new date, only one change per day), while Table 2 holds the ownership info.

I want results like this -

Date, Owner, FishCount
2010-01-01, 1, 100
2010-01-01, 2, 110
2010-01-02, 1, 99
2010-01-02, 2, 111,
etc

Any help would be greatly appreciated. If am I posting in the wrong subject area, please let me know?

 

[IanWaterman]

Try

SELECT DATEADD(dd, DATEDIFF(dd, 0, datefield), 0), owner, sum(fish)
from yourtable
group by
DATEADD(dd, DATEDIFF(dd, 0, datefield), 0), owner

Ian