Post reply

Combining AVG and SUM functions

  • February 15, 2017 at 11:21 am

    #321675

    I have a derived column in my stored procedure where i process the amount of days in an ordering process

    [ORDER PROCESS] = SUM(CASE WHEN (DATEDIFF(dd,convert(datetime,orders.ordershipped,121),getdate()) -
    (2 * DATEDIFF(wk,convert(datetime,orders.orderarrived,121),getdate()))) < 2 AND orders.closed <> '' THEN 1 ELSE 0 END)

    but i need to find the average amount of days ; when i do ....AVG(SUM(CASE WHEN (DATEDIFF(dd,convert(datetime,orders.ordershipped.... it is not legal syntax as I cannot combine average and sum aggregate functions. Is there another trick for this?
    Thanks

  • February 15, 2017 at 12:28 pm

    #1928697

    Use a CTE and do a SUM of your intial group, and then do the AVG on the data within the CTE.

    Without DDL and sample data though, can't give you much more.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • February 15, 2017 at 12:58 pm

    #1928704

    Depending on the size of the data and the SQL Server version and options and a lot of other factors, it may be faster to throw the summed values into a temp table and then do the AVG on the temp table or table variable.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

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