CODEWITHSUNDEEP
sqlsql-serverdistinct
stacey528
stacey528

Reputation: 13

SQL Query SUM and Divide by Distinct Date Count

I assistance, I am looking for the sum of a data field and then want to divide it by the number of distinct dates in that field.

SUM(CASE WHEN dateResolved IS NOT NULL 
         THEN 1 ELSE 0 
    END) / DISTINCT(dateResolved) AvgPerDay

If there are 32 dates in dateResolved, with 5 distinct dates, I want it to return 6.4.

Upvotes: 1

Views: 353

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

I would do this as:

SUM(CASE WHEN dateResolved IS NOT NULL 
         THEN 1.0 ELSE 0 
    END) / COUNT(DISTINCT dateResolved) as AvgPerDay

But this is more simply phrased as:

COUNT(dateResolved) * 1.0 / COUNT(DISTINCT dateResolved) as AvgPerDay

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

By default it does integer division you need :

SUM(CASE WHEN dateResolved IS NOT NULL 
         THEN 1 ELSE 0 
    END) * 1.0 / COUNT(DISTINCT dateResolved) AvgPerDay

However simply count would also work :

COUNT(dateResolved) * 1.0 / COUNT(DISTINCT dateResolved) AvgPerDay

COUNT(dateResolved) will ignore null values.

Upvotes: 3

Related Questions