title: How to use output of inner query in SQL
body: <p>I have 2 tables in Oracle 11g database i.e. <code>EARNEDRATING</code> and <code>EMPLOYEE</code>.</p>
  
  <pre><code>EMP_NUM    RTG_CODE     EARNRTG_DATE
  ------------------------------------
  101          CFI         18-Feb-98
  101          CFII        15-Dec-05
  101          INSTR       8-Nov-93
  101          MEL         23-Jun-94
  101          SEL         21-Apr-93
  
  
  EMP_NUM EMP_LNAME   EMP_FNAME
  -----------------------------
  101     Kolmyoz      George
  </code></pre>
  
  <p>I want to write the query which find emp_num, emp_fname, emp_lname for all employee and their number of earned ratings.</p>
  
  <p>I tried like this </p>
  
  <pre><code>select 
      er.emp_num, e.emp_fname, e.emp_lname, 
      count(er.rtg_code) as "Number of Earned Rating" 
  from 
      earnedrating er 
  join 
      employee e on er.emp_num=e.emp_num  
  group by 
      er.emp_num;
  </code></pre>
  
  <p>but getting this error </p>
  
  <blockquote>
    <p>not a GROUP BY expression</p>
  </blockquote>