CODEWITHSUNDEEP
sqlsql-server-2008selectdistinct-values
user2286756
user2286756

Reputation: 211

How to SELECT COUNT DISTINCT?

I have problem to select COUNT distinct: The answer I need is 6. The Time different is < 300Sec in count as 1. My sql :

SELECT COUNT(DISTINCT(cast([Date] as date) + cast([time] as datetime)))
FROM [Table1]WHERE [DATE] = '2013-06-22'
AND ErrCode = 'Scrubber Failure'AND Frequency = 1

Upvotes: 3

Views: 858

Answers (1)

peterm
peterm

Reputation: 92785

UPDATED

SELECT COUNT(DISTINCT 
             ROUND(CONVERT(DECIMAL(20, 4), 
                     DATEDIFF(minute, 
                              CONVERT(datetime, '2013-01-01 00:00:00', 120), 
                              CONVERT(datetime, date + ' ' + time, 120))) / 5, 0)) n
  FROM table1
 WHERE date = '2013-06-22'
   AND errcode = 'Scrubber Failure'
   AND frequency = 1

Output:

| N |
-----
| 6 |

Here is SQLFiddle demo.

Upvotes: 1

Related Questions