Post reply

Average without zero

  • September 16, 2010 at 10:03 am

    #227995

    Hopefully a simple question.

    I have a group of data such as this:

    Machine Goal

    111 0

    112 0

    113 800

    114 810

    I would like to exclude the machines that have no values for the Goal column, thus returning the value of 805 instead of 402.5. Any ideas?

    Thanks for the help.

  • September 16, 2010 at 10:17 am

    #1222330

    SELECT AVG(GOAL) FROM dbo.table

    WHERE GOAL > 0

  • September 16, 2010 at 10:18 am

    #1222333

    DROP TABLE #Sample

    CREATE TABLE #Sample (Machine INT, Goal INT)

    INSERT INTO #Sample (Machine, Goal)

    SELECT 111, 0 UNION ALL

    SELECT 112, 0 UNION ALL

    SELECT 113, 800 UNION ALL

    SELECT 114, 810

    SELECT AVG(d.Goal)

    FROM (

    SELECT Machine = NULLIF(Machine,0), Goal = NULLIF(Goal, 0)

    FROM #Sample

    ) d

    Recommended reading for this is NULLIF() and also handling of null values in aggregation.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 16, 2010 at 10:38 am

    #1222363

    Thanks for the replies.

    As usual, the specs have changed and now I no longer have to report those machines without goals.

    Bill

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply