Solved Union Query Average Grouped

[E9-Tech]

I have a union query to, both tables have a date formatted as "yyyy mm" to group records by month, I need to combine the PaidTime from tblDuties and PaidTimeAnnualLeave from tblAnnualLeave as AveragePaidTime

SELECT Format([DutyDate],"yyyy mm") AS YearMonth,
       Avg(tblDuties.PaidTime) AS AveragePaidTime,
       Avg(tblDuties.SpreadTime) AS AverageSpreadTime,
       Avg(tblDuties.BreakTime) AS AverageBreakTime,
       Avg(tblDuties.Overtime) AS AverageOvertime
FROM tblDuties
GROUP BY Format([DutyDate],"yyyy mm")

UNION SELECT Format([AnnualLeaveDate],"yyyy mm") AS YearMonth,
       Avg(tblAnnualLeave.PaidTimeAnnualLeave) AS AveragePaidTime,
       Null AS AverageSpreadTime,
       Null AS AverageBreakTime,
       Null AS AverageOvertime
FROM tblAnnualLeave
GROUP BY Format([AnnualLeaveDate],"yyyy mm")
ORDER BY YearMonth;

However I get two different entries when rinning the query, the below example should combine 2024 12 and 2025 01.

What would be the correct code?

 

[GaP42]

A UNION query does not merge records, it combines the individual records from each source into one common form/presentation. Hence you have 2 records for those YearMonth.

To now get the combined amounts, you write an aggregate query based upon the union query.
What would be the rule for combining average paid time values from the two records in the same month? Averages don't sum correctly if the number of instances on which they are based are not the same. You may need a count from each of the queries comprising the union query to overcome that problem.

 

[arnelgp]

not tested:

SELECT YearMonth, 
       Avg(PaidTime) AS AveragePaidTime, 
       Avg(SpreadTime) AS AverageSpreadTime,
       Avg(BreakTime) AS AverageBreakTime,
       Avg(tblDuties.Overtime) AS AverageOvertime 
FROM (
SELECT Format([DutyDate],"yyyy mm") AS YearMonth,
       tblDuties.PaidTime,
       tblDuties.SpreadTime,
       tblDuties.BreakTime,
       tblDuties.Overtime 
FROM tblDuties
UNION ALL 
SELECT Format([AnnualLeaveDate],"yyyy mm") AS YearMonth,
       tblAnnualLeave.PaidTimeAnnualLeave,
       Null AS AverageSpreadTime,
       Null AS AverageBreakTime,
       Null AS AverageOvertime
FROM tblAnnualLeave) 
GROUP BY YearMonth;

 

[CJ_London]

A UNION query does not merge records

It will however remove duplicates. Suggest use UNION ALL to prevent this

 

[June7]

First SELECT in UNION defines field names. The AS aliases in the second SELECT are not needed and won't even show. In other words can do just:

PaidTimeAnnualLeave, Null, Null, Null

 

[E9-Tech]

not tested:

SELECT YearMonth,
       Avg(PaidTime) AS AveragePaidTime,
       Avg(SpreadTime) AS AverageSpreadTime,
       Avg(BreakTime) AS AverageBreakTime,
       Avg(tblDuties.Overtime) AS AverageOvertime
FROM (
SELECT Format([DutyDate],"yyyy mm") AS YearMonth,
       tblDuties.PaidTime,
       tblDuties.SpreadTime,
       tblDuties.BreakTime,
       tblDuties.Overtime
FROM tblDuties
UNION ALL
SELECT Format([AnnualLeaveDate],"yyyy mm") AS YearMonth,
       tblAnnualLeave.PaidTimeAnnualLeave,
       Null AS AverageSpreadTime,
       Null AS AverageBreakTime,
       Null AS AverageOvertime
FROM tblAnnualLeave)
GROUP BY YearMonth;

This code works!
Thanks