Zusee Weekin Zusee Weekin - 1 year ago 123
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 Source

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' 

Note: The expression DueDate IS NOT NULL is redundant. The comparisons will fail if DueDate is NULL`.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download