SQL Sum Multiple values for that day

Question

Here is my table

TIME                     Status
2012-11-15 8:30:00.000   "WAS_FLOW"
2012-11-15 9:00:00.000   "WAS_FLOW"
2012-11-15 10:30:00.000  "H2_FLOW"
2012-11-11 12:14:00.00   "O23HZ_FLOW"
2012-11-11 8:00.00.000   "AZ_FLOW"
2012-11-12 9:00.000      "BZ_FLOW"

I want my results to show:

TIME         "WAS FLOW"    
2012-11-11   0
2012-11-12   0
2012-11-15   2

Answer 1

Other answers here all had slight glitches. Here is one that I hope will work straight out of the box for you.

SELECT
  DATEADD(DAY, DATEDIFF(DAY, 0, [time]), 0)             AS [date],
  SUM(CASE WHEN status = 'WAS_FLOW' THEN 1 ELSE 0 END)  AS [count]
FROM
  yourTable
GROUP BY
  DATEADD(DAY, DATEDIFF(DAY, 0, [time]), 0)

Answer 2

SELECT CAST(TIME AS date) AS TIME,
       COUNT(CASE WHEN Status = 'WAS_FLOW' THEN Status END) AS 'WAS_FLOW'
FROM dbo.your_table
GROUP BY CAST(TIME AS date)

Demo on SQLFiddle