MySQL group by select aggregate and one particular record at the same time

Question

This table has been grouped by EMPLOYER-NAME, JOB_TITLE, WORKSITE_CITY, WORKSITE_STATE and IN_YEAR, calculated AVG_ANNUAL_SALARY.

What I want to do in my next query is:

select EMPLOYER-NAME, JOB_TITLE, WORKSITE_CITY, WORKSITE_STATE,
    AVG(AVG_ANNUAL_SALARY) AS ANNUAL_SALARY,
    "Most recent year(could be 2015 or 2014 or 2013 or 2012)'s salary"
from table
group by EMPLOYER-NAME, JOB_TITLE, WORKSITE_CITY, WORKSITE_STATE

I'm new in mysql, could someone help me retrieve the part in double quote? Thanks!

Answer 1

Try this query:

select t1.EMPLOYER-NAME, t1.JOB_TITLE, t1.WORKSITE_CITY, t1.WORKSITE_STATE,
    AVG(t1.AVG_ANNUAL_SALARY) AS ANNUAL_SALARY, t2.AVG_ANNUAL_SALARY AS MOST_RECENT_SALARY
from table t1
group by EMPLOYER-NAME, JOB_TITLE, WORKSITE_CITY, WORKSITE_STATE
inner join
(
    select EMPLOYER-NAME, JOB_TITLE, WORKSITE_CITY, WORKSITE_STATE, AVG_ANNUAL_SALARY
    from table r1
    where r1.IN_YEAR = (
        select max(r2.IN_YEAR) from table r2
        where r1.EMPLOYER-NAME = r2.EMPLOYER-NAME and r1.JOB_TITLE = r2.JOB_TITLE
            and r1.WORKSITE_CITY = r2.WORKSITE_CITY
            and r1.WORKSITE_STATE = r2.WORKSITE_STATE
    )
) t2
ON t1.EMPLOYER-NAME = t2.EMPLOYER-NAME and t1.JOB_TITLE = t2.JOB_TITLE
    and t1.WORKSITE_CITY = t2.WORKSITE_CITY and t1.WORKSITE_STATE = t2.WORKSITE_STATE

Explanation:

The inner query finds for each group the average annual salary in the most recent year. This temporary table is then joined to the table from your original query by the combination of EMPLOYER-NAME, JOB-TITLE, WORKSITE_CITY and WORKSITE_STATE.

Note:

You might be able to get away with a greatly simplified query by assuming that MySQL will only return the first row of a GROUP BY by default. q.v. this SO post. But I would shy away from doing this in production because you never know when or where it might not be supported.