June 26, 2009 at 6:20 am
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
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
June 26, 2009 at 6:50 am
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