Zusee Weekin Zusee Weekin - 4 months ago 12
SQL Question

MSSQL query to get count value each day

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'


Any help would be appreciated.

Answer

I think you just need a group by:

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`.

Comments