CODEWITHSUNDEEP
sqlsql-server
Zusee Weekin
Zusee Weekin

Reputation: 1358

SQL Server 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.

Upvotes: 2

Views: 361

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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

Upvotes: 4

Related Questions