Access SQL：无法合并男女分组平均薪资查询的问题求助

Hey there! Let's sort this out for you since you're new to Access SQL. The issue with using UNION here is that it stacks results vertically (adds rows), but you want to combine them horizontally into columns—so that's why that approach wasn't working. Here are two straightforward methods to get the result you need:

This is the most efficient way since it only scans your employee table once. Use AVG() with Access's IIF() function to calculate averages for each gender within the same query:

SELECT 
    JobID,
    AVG(IIF(Gender = 'Male', Salary, NULL)) AS MaleAvgSalary,
    AVG(IIF(Gender = 'Female', Salary, NULL)) AS FemaleAvgSalary
FROM 
    YourEmployeeTable -- Replace with your actual table name
WHERE 
    JobID = [TargetJobID] -- Replace with your specified JobID; remove this line to get results for all JobIDs
GROUP BY 
    JobID;

How it works: #

• IIF(Gender = 'Male', Salary, NULL) returns the employee's salary only if they're male; otherwise, it returns NULL. The AVG() function ignores NULL values, so it only calculates the average for male salaries in that column.
• The same logic applies to the female average column.
• GROUP BY JobID ensures you get one row per JobID, with both average salaries side by side.

If you want to reuse your original two queries, you can join them on JobID to combine their results horizontally. Let's assume your original queries look like this:

-- Original male average query
SELECT JobID, AVG(Salary) AS MaleAvgSalary
FROM YourEmployeeTable
WHERE Gender = 'Male' AND JobID = [TargetJobID]
GROUP BY JobID;

-- Original female average query
SELECT JobID, AVG(Salary) AS FemaleAvgSalary
FROM YourEmployeeTable
WHERE Gender = 'Female' AND JobID = [TargetJobID]
GROUP BY JobID;

Combine them with a LEFT JOIN (use INNER JOIN only if every JobID has both male and female employees):

SELECT 
    COALESCE(m.JobID, f.JobID) AS JobID,
    m.MaleAvgSalary,
    f.FemaleAvgSalary
FROM 
    (
        SELECT JobID, AVG(Salary) AS MaleAvgSalary
        FROM YourEmployeeTable
        WHERE Gender = 'Male' AND JobID = [TargetJobID]
        GROUP BY JobID
    ) AS m
LEFT JOIN 
    (
        SELECT JobID, AVG(Salary) AS FemaleAvgSalary
        FROM YourEmployeeTable
        WHERE Gender = 'Female' AND JobID = [TargetJobID]
        GROUP BY JobID
    ) AS f
ON m.JobID = f.JobID;

Note: #

• COALESCE() ensures the JobID is displayed even if one gender has no employees for that JobID (it picks the non-null JobID value from either subquery).

内容的提问来源于stack exchange，提问作者James May