CODEWITHSUNDEEP
sqlms-access
Sasha1001
Sasha1001

Reputation: 31

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

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"?

Upvotes: 1

Views: 56

Answers (2)

Sola Oshinowo
Sola Oshinowo

Reputation: 609

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;

Upvotes: 0

June7
June7

Reputation: 21370

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.

Upvotes: 3

Related Questions