SQL Query for total time water pump on between dates

Question

I have a water pump that the run state is stored in a mssql database every 10 seconds along with the time of day. The pump cycles often through out the day. How do I query for the total time the pump was on during the day?

Answer 1

SELECT COUNT(*) FROM pump WHERE state = on

Take the result, and multiply by 10 to get time on in seconds?

As mentioned in the comments, if you also select the date and GROUP BY date you can get counts per day.

Answer 2

Group by day, count the number of records, and multiply by 10:

select 
   year = datepart(y,datefield)
,  dayofyear = datepart(dy,datefield)
,  minuteson = count(*)*10
from pump
where state = 'on'
group by datepart(y,datefield), datepart(dy,datefield)