I need to count the number of event in each day between given time period. This is what I have tried, but it counts total events in given period. I need to count each day amount. Is this something possible to do in one query? For ex: 2016-01-01->2 , 2016-01-02->1 etc.
SELECT count(ID)as events FROM table WHERE VehicleName='testvehicle'
AND DueDate IS NOT NULL AND DueDate >= '2016-01-01' AND DueDate <= '2016-12-31'
I think you just need a
SELECT DueDate, count(ID) as events FROM table WHERE VehicleName = 'testvehicle' AND DueDate >= '2016-01-01' AND DueDate <= '2016-12-31' GROUP BY DueDate;
Note: The expression
DueDate IS NOT NULL is redundant. The comparisons will fail if
DueDate is NULL`.