Post reply

Average Query

  • June 26, 2009 at 6:20 am

    #131644

    Hi All,

    I am a bit stuck with a problem which I hope someone can assist with resolving.

    I have a master table: MyMasterTable with columns MyMasterTableID INT (PK) and TransactionCount INT.

    Then a detail table: MyDetailTable with columns fkMyMasterTableID INT (FK) and a bunch of other columns.

    Now I write a query..

    SELECT *

    FROM MyDetailTable

    JOIN MyMasterTable ON MyMasterTable.MyMasterTableID = MyDetailTable.fkMyMasterTableID

    The problem I am having is with TransactionCount from MyMasterTable. I need to get an average of TransactionCount as AvgTransactionCount per detail record, so when sent through to a report, the report can do a SUM of the AvgTransactionCount getting back to the original value.

    In the above scenario, this is all good as you can simply use the AVG keyword. It gets tricking when I start to group by other columns at the detail level. Like Categories, etc... Then AVG does not work in it's simplest form for me anymore.

    Is anyone able to assist with a nice trick to get an accurate average transaction count per detail record so the data is correctly rendered in a report?

    I hope I explained myself clearly enough here 🙂

    TIA

  • June 26, 2009 at 6:46 am

    #1015975

    Example data and expected output would be cool - especially if you write it all in SQL.

    Otherwise, depending on the data and requirements, you might like to look at AVG DISTINCT expression or AVG expression with the OVER (PARTITION BY... clause.

    See http://msdn.microsoft.com/en-us/library/ms177677(SQL.90).aspx and http://msdn.microsoft.com/en-us/library/ms189461(SQL.90).aspx for syntax and a brief guide.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • June 26, 2009 at 6:50 am

    #1015977

    Greetings Michael,

    What you might be able to do is create a computed column based on a sub select to hold your average.

    SELECT

    *,

    (

    SELECT

    AVG(m2.TransactionCount)

    FROM MyMasterTable m2

    WHERE d.fkMyMasterTableID = m2.MyMasterTableID

    ) AS DetailAverage

    FROM MyDetailTable d

    JOIN MyMasterTable m ON d.fkMyMasterTableID = m.MyMasterTableID

    This way, it does not matter how you organize your data, it will still return the average based on your criteria.

    Have a good day.

    Terry Steadman

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

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