SQL Union query to average results

[Autoeng]

I have the following SQL Union query which selects records that meet criteria set in qryR2IPDNN and calculates the average grouped by name and also provides the average for all.

If data is...
Jill - 2
Jill - 3
Jill - 6
Ann - 1
Ann - 3

I get a return from this query of...
Average - 3
Jill - 3.66
Ann - 2

What do I need to do to only return the average of all (in this example = 3)?

Select Name, Avg(weekdays) As AvgDif 
From qryR2IPDNN
Group By Name 
UNION Select "Average" As Name, Avg(weekdays) As AvgDif 
From R2IPDNN
ORDER BY Name;

 

[pdx_man]

Well, maybe this is what you're looking for:

Select Avg(weekdays) As AvgDif
From qryR2IPDNN

You don't want to have it broken down by changes in name and you cannot have differing numbers of columns with UNION statements, so ...

'cause right now, you are getting them broken out by the names and then an average across both, so, I'm not sure exactly what more you're looking for. It is giving you an answer of 3 for your Average row.

 

[Autoeng]

Haven't tried it yet PDX (not at work) but to clarify I just want the average of all returned. Currently I get the average of all and then each person's average.

Currently
Average = 3
Jill = 3.66
Ann = 2

What I want
Average = 3

 

[pdx_man]

Then the query I posted should get you what you need.

 

[Autoeng]

Worked perfectly PDX. Thanks!