title: SQL Oracle query to compare male vs. female salaries for each job at a company
body: <p>return one row for each <strong>Job_ID</strong> with the following columns:</p>
  
  <p>•   J<strong>ob ID</strong></p>
  
  <p>•   <strong>Average female service length</strong> in that job in years rounded to the tenth of a year</p>
  
  <p>•   <strong>Average Female Salary</strong> for that job</p>
  
  <p>•   <strong>Average male service length</strong> in that job in years rounded to the tenth of a year</p>
  
  <p>•   <strong>Average Male Salary</strong> for that job</p>
  
  <p>•  <strong>The difference in average male and female salaries</strong> for that job such that a positive number means the average female salary is higher and negative number means the average male salary is higher.</p>
  
  <p><strong>TABLE</strong></p>
  
  <p>HR.EMPLOYEES</p>
  
  <pre><code>Name           Null     Type         
  -------------- -------- ------------ 
  EMPLOYEE_ID    NOT NULL NUMBER(6)    
  FIRST_NAME              VARCHAR2(20) 
  LAST_NAME      NOT NULL VARCHAR2(25) 
  EMAIL          NOT NULL VARCHAR2(25) 
  PHONE_NUMBER            VARCHAR2(20) 
  HIRE_DATE      NOT NULL DATE         
  JOB_ID         NOT NULL VARCHAR2(10) 
  SALARY                  NUMBER(8,2)  
  COMMISSION_PCT          NUMBER(2,2)  
  MANAGER_ID              NUMBER(6)    
  DEPARTMENT_ID           NUMBER(4)    
  GENDER                  CHAR(1) 
  </code></pre>
  
  <p><strong>What I Have So Far</strong>  but this is returning an M &amp; F row for each job_id i need M &amp; F columns</p>
  
  <pre><code>SELECT gender, job_id, ROUND(AVG(salary),0) as avg_job_salary,
  (SELECT ROUND(AVG(salary),0)
  FROM hr.employees 
  WHERE gender = 'M') AS avg_m_salary, (SELECT ROUND(AVG(salary),0) 
  FROM hr.employees 
  WHERE gender = 'F') AS avg_f_salary,
  ROUND(AVG(days_of_svc/365),1) AS avg_years_svc
  FROM (SELECT  job_id, salary, gender, (SYSDATE-hire_date) AS  days_of_svc 
  FROM hr.employees)
  GROUP BY job_id,gender
  ORDER BY job_id, gender;
  </code></pre>
  
  <p><strong>or version 2</strong></p>
  
  <pre><code>SELECT gender, job_id, ROUND(AVG(salary),0) as avg_job_salary, 
  ((SELECT ROUND(AVG(salary),0)
  FROM hr.employees 
  WHERE gender = 'F') - (SELECT ROUND(AVG(salary),0) 
  FROM hr.employees 
  WHERE gender = 'M')) as diff,
  ROUND(AVG(days_of_svc/365),1) AS avg_years_svc
  FROM (SELECT  job_id, salary, gender, (SYSDATE-hire_date) AS  days_of_svc 
  FROM hr.employees)
  GROUP BY job_id,gender
  ORDER BY job_id, gender;
  </code></pre>
  
  <p><strong>sample expected results rows</strong></p>
  
  <pre><code>JOB_ID    F_AVG_LENGTH F_AVG_SAL M_AVG_LENGTH M_AVG_SAL DIFFERENCE
  ------    ------------ --------- ------------ --------- ----------
  MAILCLERK         24.1     48000         23.4     47000       1000
  CASHIER            4.6     12000          4.4     13500      -1500
  </code></pre>