CODEWITHSUNDEEP
sqlsql-server
VAAA
VAAA

Reputation: 15039

SQL how to do an average when the cell has a value greater than 0

I have a SQL Query that gives an Average of Lunch times between two dates.

,AVG(DATEDIFF(SECOND, 0,  lunchduration) / (60.0 * 60.0)) as avglunchduration

Right now I'm getting a very low average value because it's taking into consideration values = 0, so if in a day the employee didn't go to have lunch then my average is going lower...

What I need is only to do the average when the "lunchduration" column has a value greater than 0.

Any clue?

Upvotes: 2

Views: 2388

Answers (2)

user4413591
user4413591

Reputation: 

SELECT AVG(fields)
FROM tables
WHERE lunchduration > 0

Where fields is the fields you want and tables are the tables the fields are in.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

The easiest way is probably to use NULLIF():

AVG(NULLIF(DATEDIFF(SECOND, 0,  lunchduration) / (60.0 * 60.0), 0))

This returns NULL if the duration is zero, and NULL values are ignored by AVG().

A more explicit case statement is more generalizable:

AVG(CASE WHEN DATEDIFF(SECOND, 0,  lunchduration) > 0
         THEN DATEDIFF(SECOND, 0,  lunchduration) / (60.0 * 60.0)
    END)

Upvotes: 4

Related Questions