MySQL group using select aggregate and one specific record at a time
This table has been grouped with EMPLOYER-NAME, JOB_TITLE, WORKSITE_CITY, WORKSITE_STATE and IN_YEAR, calculated by AVG_ANNUAL_SALARY.
What I want to do in the following query:
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) salary"
from table
group by EMPLOYER-NAME, JOB_TITLE, WORKSITE_CITY, WORKSITE_STATE
I'm new to mysql, can anyone help me get the part in double quotes? Thank!
source to share
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:
An internal query finds the average annual salary for each group in the last year. This temporary table is then connected to the table from your original query combinations EMPLOYER-NAME
, JOB-TITLE
, WORKSITE_CITY
and WORKSITE_STATE
.
Note:
You may be able to avoid the simplified query by assuming that MySQL will only return the first row GROUP BY
by default. g. this is SO post . But I'm not going to do this in production because you never know when or where it might not be supported.
source to share