Combining two SELECT SQL MS Access queries into a single query to make a table with two columns

user_17806773 0
Viewed 49

I have two queries in SQL for MS Access. First one is:

SELECT AVG(Salary) AS AverageSalary FROM Table WHERE EducationLevel = 1;

and second query:

SELECT AVG(Salary) AS AverageSalary2 FROM Table WHERE EducationLevel = 2;

How can I combine them into one query to produce a table with two columns, labelled "AverageSalary" and "AverageSalary2"?

ms-access sql
2 Answers

Can accomplish in one query. Consider:

Conditional expressions:

SELECT Avg(IIf(EducationLevel = 1, Salary, Null)) AS AverageSalary, 
       Avg(IIf(EducationLevel = 2, Salary, Null)) AS AverageSalary2
FROM [Table];

Or a CROSSTAB:

TRANSFORM Avg(Table.Salary) AS AvgOfSalary
SELECT 1 AS R
FROM [Table]
GROUP BY 1
PIVOT "AverageSalary" & [EducationLevel];

Or build a report and use its Sorting & Grouping features with aggregate calculations. This easily allows display of detail records as well as summary data.

user_76071900 
Below query, if the educational level field is a number field

 

     SELECT Avg(Switch([EducationalLevel]=1,[salary])) AS AverageSalary, 
     Avg(Switch([EducationalLevel]=2,[salary])) AS AverageSalary2
     FROM salary;

Below query if the educationallevel field is a text field

   SELECT Avg(Switch([EducationalLevel]="1",[salary])) AS AverageSalary, 
    Avg(Switch([EducationalLevel]="2",[salary])) AS AverageSalary2
    FROM salary;
user_116086790
Related