title: How can I find AVG of a group in Hive and compare against it
body: <p>I'm not sure the title explains my problem very well, I'll detail it correctly here.</p>
  
  <p>I have a table called 121_salaries which has columns empname, address, jobtitle, department and salary.</p>
  
  <p>I want to find the average salary of each job title, then display the information of the employees who's salary is significantly higher than the rest, with that job title.</p>
  
  <p>The query I have so far is:</p>
  
  <pre><code>SELECT * FROM (SELECT jobtitle, salary, AVG(salary) OVER() AS avgSal FROM 121_salaries) t WHERE salary &gt; 1.5*avgSal;
  </code></pre>
  
  <p>This query gives me results but it averages the whole salary column instead of finding the average of each job title.</p>
  
  <p>Any help is appreciated and thank you in advance!</p>