Total Average, ignoring GROUP BY

Question

I created a query in Microsoft Access such as the one below:

SELECT LoanType
,Avg(Loan Amount)
,Avg(Loan Rate)

FROM Table1
GROUP BY LoanType

The output is as you would expect, the average loan amount and the average loan rate for each loan type.

However, I'd like for my Access Report to calculate the average of all the loans, regardless of loan type, and place this row at the very bottom. Using the Report view in Access, you can add a "Totals" row where you can write a formula such as COUNT(), SUM(), AVG(). But as you know, calculating an average of an average goes against basic math.

I'm assuming I have to create this "Totals" row at the SQL/Query level. But I can't seem to figure it out. Any input would be greatly appreciated!

If you SUM all the SUM's of the amounts per type & SUM the COUNT's for each type. Then divide that total sum by the total count. Wouldn't that give a total average? Or you could UNION ALL to a select for total averages. — LukStorms, Jul 18 '18 at 22:00
In the "Totals" row you're allowed use COUNT(), but I'm pretty certain that will count the number of rows in the Report, not the underlying data. However, the UNION ALL function does work. Thanks! — MarioS, Sep 18 '18 at 15:47

score 4 · Accepted Answer · answered Jul 18 '18 at 21:54

You can use UNION ALL to add a row with a similar query, just without the GROUP BY and a NULL for LoanType (or any other value you like as long as it's implicitly castable to the data type of LoanType).

SELECT LoanType,
       Avg(Loan Amount)
       Avg(Loan Rate)
       FROM Table1
       GROUP BY LoanType
UNION ALL
SELECT NULL,
       Avg(Loan Amount)
       Avg(Loan Rate)
       FROM Table1;

score 1 · Answer 2 · answered Jul 19 '18 at 02:22

Of yourse you can do exactly what you described: Build a query that calculates the averages by LoanType, build a report on this query and calculate an average in the report footer. Unfortunately, this "total average" will be an average of averages, but I guess you want an average over all records. To achieve this:

Base your report on Table1.
Create a group to group by LoanType.
Calculate the averages by LoanType in the group footer.
If you don't want to see the details, set the details section to be invisible.
Calculate the "total averages" in the report footer.

Total Average, ignoring GROUP BY

2 Answers2