title: How to find average after joining two datasets and grouping, in Pig
body: <p>I have two data sets, EmployeeDetail that contains 4 columns(id, name, gender, location) and SalaryDetail(id, salary). I joined both dats sets and grouped them as location.</p>
  
  <pre><code>EmpDetail = load '/Users/bmohanty6/EmployeeDetails/EmpDetail.txt' as (id:int, name:chararray, gender:chararray, location:chararray);
  SalaryDetail = load '/Users/bmohanty6/EmployeeDetails/EmpSalary.txt' as (id:int, salary:float);                                     
  JoinedEmpDetail = join EmpDetail by id, SalaryDetail by id;                                                                         
  GroupedByLocation = group JoinedEmpDetail by location; 
  </code></pre>
  
  <p>DUMP GroupedByLocation gives me the correct result I expect. Now when I try to take average using below line,</p>
  
  <pre><code>AverageSalary = foreach GroupedByLocation generate group, AVG(SalaryDetail.salary);
  </code></pre>
  
  <p>It throws below error.</p>
  
  <pre><code>&lt;line 11, column 58&gt; Could not infer the matching function for org.apache.pig.builtin.AVG as multiple or none of them fit. Please use an explicit cast.
  </code></pre>
  
  <p>I also tried in following way. But got same error.</p>
  
  <pre><code>AverageSalary = foreach GroupedByLocation {
    Sum = SUM(SalaryDetail.salary);
    Count = COUNT(SalaryDetail.salary);
    avgSal = Sum/Count;
    generate group as location, avgSal;
    };
  </code></pre>
  
  <p>This time error was:</p>
  
  <pre><code>Could not infer the matching function for org.apache.pig.builtin.SUM as multiple or none of them fit. Please use an explicit cast.
  </code></pre>
  
  <p>Can anyone please suggest me a correct way of doing it.</p>
  
  <p>Thanks <strong>Sivasakthi Jayaraman</strong> for answering my question. </p>
  
  <pre><code>AverageSalary = foreach GroupedByLocation generate group, AVG(JoinedEmpDetail.SalaryDetail::salary);
  </code></pre>
  
  <p>This gives me the average salary per each location.
  Now I tried to find out average salary per gender in each <code>location</code>. So I tried to group by <code>gender</code> inside <code>GroupedByLocation</code> variable. But facing some issues in it.</p>
  
  <pre><code>GroupdByGender = foreach GroupedByLocation { 
  genderGrp = group JoinedEmpDetail by JoinedEmpDetail.EmpDetail::gender;
  avgSalary = foreach genderGrp generate group, AVG(JoinedEmpDetail.SalaryDetail::salary);
  generate group as location, JoinedEmpDetail.EmpDetail::gender, avgSalary;
  };
  </code></pre>
  
  <p>I got this error</p>
  
  <pre><code>Syntax error, unexpected symbol at or near 'JoinedEmpDetail'
  </code></pre>
  
  <p>Can anyone please help.</p>