Post reply

SUM, then AVG data. Works in T-SQL, but not from within Excel?

  • May 28, 2008 at 9:17 am

    #187183

    I have a query that works in the Microsoft SQL Server Management Studio, but not as a database query in Excel, with an ODBC connection.

    First, it builds a table in memory with the TABLE datatype, that contains the daily summary of materials used from our inventory transaction table.

    Then, a SELECT query follows that shows a list of those materials, with the average of the daily summaries just built (see above).

    My report needs to show the average of the daily total usage of a given material, not just the average of each issue over the course of a day.

    Why does this not work as a database query in Excel, and are there different ways of approaching this problem that would work?

  • May 28, 2008 at 11:16 am

    #821343

    Believe it or not - you should be able to do all of that in a single query statement these days, using an AVERAGE() OVER on the summed expressions.

    Something like:

    ;with SumCTE as (

    select dayvalue, itemID, sum(itemvalue) as dayitemTot

    from myTable

    group by davalue,itemID)

    select SumCTE,*, average(dayitemTot) over (Partition by DayValue)

    from SumCTE.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

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