CTE to find average

  • I am trying to get the average for number of Ids per day

    But , some how I get "0".

    Can anyone suggest

    With CTE1

    As

    (

    SELECT

    count([ID]) as No_OF_IDS,

    Days

    FROM [Proddata]

    LEFT OUTER JOIN SupplyData as S ON Proddata.Group = S.Group

    GroupBy Days

    )

    Select Sum(ISNULL(No_of_IDS,0))/Sum(ISNULL(Days,0))

    from CTE1

  • How about:

    SELECT x.Days, AVG(x.No_Of_IDs) As AvgCount

    FROM

    (SELECT Days,

    count([ID]) as No_OF_IDS

    FROM [Proddata]

    LEFT OUTER JOIN SupplyData as S ON Proddata.Group = S.Group

    GroupBy Days

    ) x

    GROUP BY x.Days;

    No CTE required.

  • sharonsql2013 (12/6/2013)


    I am trying to get the average for number of Ids per day

    But , some how I get "0".

    Can anyone suggest

    It giving zero because there might be not data matching against the join you are using, try to change the query to the inner join to check if there any results you find, 2ndly there is not need to CTE, desire result can be achieve through a simple query.

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

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