CODEWITHSUNDEEP
sqlsql-servert-sql
elzool
elzool

Reputation: 3

Distinct counts with datediffs are not so distinct

I'm looking to return a count of a singular ID and the number of minutes since the record was created.

I have this as a query:

SELECT DISTINCT
  batchid AS 'ID'
, COUNT(batchid) AS 'Count'
, DATEDIFF (MINUTE,CONVERT(char(10),MIN([createdOn]), 108),CONVERT(char(10),GETDATE(), 108))
FROM [UpdateRecords]
WHERE [status] = 'Pending'
GROUP BY batchId,createdOn
ORDER BY batchId

It returns the ID, the Count and the Minutes.

ID      Count  Minutes
171836  1      28
171836  4      28
171836  10     28
171836  21     28

The problem arises that the same ID will appear multiple times. When added together, the count for that ID is accurate.

I would like a distinct count, by ID, along with the minutes



ID      Count  Minutes
171836  36     28

How can I do this using minutes? Can this be done displaying hours and minutes?

Upvotes: 0

Views: 59

Answers (2)

Jen R
Jen R

Reputation: 1535

You need to group by the same items in your Select:

SELECT 
batchid AS 'ID'
, COUNT(batchid) AS 'Count'
, DATEDIFF (MINUTE,CONVERT(char(10),MIN([createdOn]), 108),CONVERT(char(10),GETDATE(), 108))
FROM [UpdateRecords]
WHERE [status] = 'Pending'
GROUP BY batchId, DATEDIFF (MINUTE,CONVERT(char(10),MIN([createdOn]), 108),CONVERT(char(10),GETDATE(), 108))
ORDER BY batchId

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

I think you want something like this:

SELECT batchid AS ID,
       COUNT(*) AS Count,
       SUM(DATEDIFF(MINUTE, CONVERT(time, createdOn), CONVERT(time, GETDATE()))
FROM UpdateRecords
WHERE status = 'Pending'
GROUP BY batchId
ORDER BY batchId

Upvotes: 1

Related Questions