title: How to use Oracle JOIN with GROUP BY effectively so that I can select multiple columns from both tables and applying Aggregate function
body: <p>I have Database Schema as -</p>
  
  <pre><code>create table Manufacturer (mid integer primary key, name varchar(20), address varchar(56));
  
  create table Supplies(sid integer references Supplier(sid), pid integer references Product(pid));
  </code></pre>
  
  <p>I have a problem which states-</p>
  
  <blockquote>
    <p><strong>List the ids and names of manufacturers and the count of products manufactured by each</strong></p>
  </blockquote>
  
  <p>So, I tried GROUP BY with JOIN but it is failing when I try to show <code>mid,name,count(pid)</code> together by showing <code>not a GROUP BY expression error</code>.</p>
  
  <p>But I have successfully executed the following-></p>
  
  <pre><code>select name,count(m2.pid) from Manufacturer m1 inner join Manufactures m2 on m1.mid=m2.mid group by name;
  </code></pre>
  
  <p>which gives me the names and corresponding Number of products successfully.
  <strong>But when I try to add mid column to show <code>mid</code> along with the other two-<code>name</code> and <code>count(pid)</code> it shows error -</strong></p>
  
  <pre><code>select m1.mid,name,count(m2.pid) from Manufacturer m1 inner join Manufactures m2 on m1.mid=m2.mid group by name;
  </code></pre>
  
  <p>Just adding the <code>m1.mid</code> forces an error!</p>
  
  <p><strong>It also gives error when I try to GROUP BY <code>mid</code> - the joining criteria!</strong></p>
  
  <pre><code>select m1.mid,name,count(m2.pid) from Manufacturer m1 inner join Manufactures m2 on m1.mid=m2.mid group by m1.mid;
  </code></pre>
  
  <p>Is it because <code>mid</code> is the joining criteria for the 2 tables ?and so I cannot use that in the GROUP BY  section? 
  I am unsure what is the error or what concept I am missing!
  Thanks in advance for your help.</p>