I need to calculate the Average by Location and then sum the Average for a Total that is displayed on a report. Following is my stored procedure so far:
SELECT tblLocn.LocnCode, tblLocn.LocnDesc,
J1.ZAvg ................. Average by locn -- want the sum of all locns here
LEFT JOIN #work ON tblLocn.LocnCode = #work.LocnCode
LEFT JOIN (SELECT #work.LocnCode,
ZAvg = Avg(#work.ZValue)
GROUP BY #work.LocnCode) J1 ON tblLocn.LocnCode = J1.LocnCode
How would I change this procedure to display either a) total average on each row, or b) location average on 1 row only so when I Sum this field it is the correct value.
I am aware that I could create a workfile with the Total value and then Join to that. Is there another alternative? Thanks
I do want the average by location as I have it coded. The problem with just summing the J1.ZAvg field is that the SELECT statement is not grouped by location but rather by DATE. This means that the J1.ZAvg field appears on each row for the location. If I sum that up, I would get an exploded value. Make sense?
If I change the J1.ZAvg to Sum(J1.ZAvg), that would give me the total I want, except that it seems I cannot use the Sum unless I use a GROUP BY clause. In Access I used the DSUM aggregate function which is not available for Access Project. I may be missing something as I'm fairly new to SQL Server.