CODEWITHSUNDEEP
sqlms-accessselectalias
tospig
tospig

Reputation: 8343

SQL nested select and aliases

The Situation

I have a typical MS Access database containing information on Companies, Pay, Employees and Positions. Some of the tables are:

  1. tbl_Report (Report_ID PK, Report_Year)
  2. tbl_Employee (Employee_ID PK)
  3. tbl_Pay (Pay_ID PK, Salary, Employee_ID FK, Report_ID FK)
  4. tbl_Position (Position_ID PK, Position, Employee_ID FK, Report_ID FK)

I have a query that selects the salary for each position and year, to produce:

qry_Salary_by_Position_Year:   (This query is parameterised to accept a 'Year').

Year | Salary | Position
------------------------
2014 |  100   |   CEO
2013 |  200   |   CEO
2014 |  300   |   CFO
2014 |  200   |  Chairman
2013 |  150   |   CEO

etc.

I then use another query to extract the top x percent of salaries for a given position:

qry_Select_Top_25:

SELECT TOP 25 PERCENT Salary, Year, Position
FROM qry_Salary_by_Position_Year;

which gives something like:

Salary | Year | Position
------------------------
 100   | 2014 |  CEO
 100   | 2014 |  CFO
 200   | 2014 |  CFO

The Question

What I would like is a final table that displays the Max(25%), Max(50%), Max(75%), Max(X%) values, grouped by Position and Year, eg:

Year | Position | 25th | 50th | 75th 
-------------------------------------
2013 |   CEO    |  10  |  30  |  75  
2014 |   CEO    |  20  |  50  |  80  
2014 |   CFO    |  15  |  30  |  90
2014 | Chairman |  20  |  25  |  30

I can do this for one percentile value using

SELECT Year, Position, Max(qry50.Salary) AS 50_Percentile
FROM (SELECT TOP 50 PERCENT qry_Salary_by_Position_Year.Salary, Year, Position 
FROM qry_Salary_by_Position_Year)  AS qry50
WHERE Position IN  (SELECT DISTINCT Position FROM qry_Salary_by_Position_Year) AND Year IN (SELECT DISTINCT Year FROM qry_Salary_by_Position_Year)
GROUP BY Year, Position;

But I can't get my head around how to construct the query with the correct aliases etc. to add in the other percentage values as other columns. Does anyone have any suggestions/comments/questions?

Edit

I may have come up with a solution that I'm now checking:

SELECT qry.Year, qry.Position, Max(qry25.Salary) AS 25_Percentile, Max(qry50.Salary) AS 50_Percentile, Max(qry75.Salary) AS 75_Percentile, Max(qry100.Salary) AS 100_Percentile
FROM 
((((qry_Salary_by_Position_Year qry
LEFT OUTER JOIN (SELECT TOP 50 PERCENT  Salary, Year, Position FROM qry_Salary_by_Position_Year) AS qry50 ON qry.Year = qry50.Year AND qry.Position = qry50.Position)
LEFT OUTER JOIN (SELECT TOP 25 PERCENT Salary, Year, Position FROM qry_Salary_by_Position_Year) AS qry25 ON qry.Year = qry25.Year AND qry.Position = qry25.Position)
LEFT OUTER JOIN (SELECT TOP 75 PERCENT Salary, Year, Position FROM qry_Salary_by_Position_Year) AS qry75 ON qry.Year = qry75.Year AND qry.Position = qry75.Position)
LEFT OUTER JOIN (SELECT TOP 100 PERCENT Salary, Year, Position FROM qry_Salary_by_Position_Year) AS qry100 ON qry.Year = qry100.Year AND qry.Position = qry100.Position)
GROUP BY qry.Year, qry.Position

Upvotes: 1

Views: 141

Answers (1)

tospig
tospig

Reputation: 8343

I think this is what I'm after:

SELECT qry.Year, qry.Position, Max(qry25.Salary) AS 25_Percentile, Max(qry50.Salary) AS 50_Percentile, Max(qry75.Salary) AS 75_Percentile, Max(qry100.Salary) AS 100_Percentile
FROM 
((((qry_Salary_by_Position_Year qry
LEFT OUTER JOIN (SELECT TOP 50 PERCENT  Salary, Year, Position FROM qry_Salary_by_Position_Year) AS qry50 ON qry.Year = qry50.Year AND qry.Position = qry50.Position)
LEFT OUTER JOIN (SELECT TOP 25 PERCENT Salary, Year, Position FROM qry_Salary_by_Position_Year) AS qry25 ON qry.Year = qry25.Year AND qry.Position = qry25.Position)
LEFT OUTER JOIN (SELECT TOP 75 PERCENT Salary, Year, Position FROM qry_Salary_by_Position_Year) AS qry75 ON qry.Year = qry75.Year AND qry.Position = qry75.Position)
LEFT OUTER JOIN (SELECT TOP 100 PERCENT Salary, Year, Position FROM qry_Salary_by_Position_Year) AS qry100 ON qry.Year = qry100.Year AND qry.Position = qry100.Position)
GROUP BY qry.Year, qry.Position

I was lead to this solution by this answer to another post: https://stackoverflow.com/a/7855015/4002530

Upvotes: 1

Related Questions