title: Eliminate certain duplicated rows after group by
body: <p>With this db:</p>
  
  <blockquote>
    <p>Chef(<em>cid</em>,cname,age),</p>
    
    <p>Recipe(<em>rid</em>,rname),</p>
    
    <p>Cooked(<em>orderid</em>,cid,rid,price)</p>
    
    <p>Customers(<em>cuid</em>,<em>orderid</em>,<em>time</em>,daytime,age)</p>
  </blockquote>
  
  <p>[cid means <strong>c</strong>hef <strong>id</strong>, and so on]</p>
  
  <p>Given orders from customers, I need to find for each chef, the difference between his age and the average of people who ordered his/her meals.
  I wrote the following query:</p>
  
  <pre><code>select cid, Ch.age - AVG(Cu.age) as Diff
  from Chef Ch NATURAL JOIN Cooked Co,Customers Cu
  where Co.orderid = Cu.orderid
  group by cid
  </code></pre>
  
  <p>This solves the problem, but if you assume that customers has their unique id, it might not work,because then one can order two meals of the same chef and affect the calculation.</p>
  
  <p>Now I know it can be answered with NOT EXISTS but I'm looking for a soultion which includes the group by function (something similar to what I wrote). So far I couldn't find (I searched and tried many ways, from select distinct , to manipulation in the where clause ,to "having count(distinct..)"  )</p>
  
  <p>Edit: People asked for an exmaple. i'm coding using SQLFiddle and it crashes alot, so I'll try my best:</p>
  
  <pre><code>cid | cuid | orderid | Cu.age
  -----------------------------
  1    333    1         20
  1    200    2         41
  1    200    5         41
  2    4      3         36
  </code></pre>
  
  <p>Let's say Chef 1's age is 50 . My query will give you 50 - (20+40+40/3) = 16 and 2/3. althought it should actually be 50 - (20+40/2) = 20. (because the guy with id 200 ordered two recipes of our beloved Chef 1.). 
  Assume Chef 2's age is 47. My query will result:</p>
  
  <pre><code>cid | Diff
  ----------
  1    16.667
  2    11
  </code></pre>
  
  <p>Another edit: I wasn't taught any particular sql-query form.So I really have no idea what are the differences between Oracle's to MySql's to Microsoft Server's, so I'm basically "freestyle" querying.(I hope it will be good in my exam as well :O ) </p>