title: JPA : Join and group by sum. MySQL query
body: <p>I've got two tables : <code>sales</code> and <code>products</code></p>
  
  <pre><code>[Products]
  +-------+-------------------+
  | id    | name              |
  +-------+-------------------+
  | 1     | product number 1  |
  | 2     | product number 2  |
  | 3     | product number 3  |
  +-------+-------------------+
  
  [Sales]
  +-------+------------+------+
  | id    | product_id | qty  |
  +-------+------------+------+
  | 1     | 2          | 100  |
  | 2     | 2          | 35   |
  | 3     | 1          | 1    |
  +-------+-------------------+
  </code></pre>
  
  <p>I'd like to display something like this with JavaEE and JPA :</p>
  
  <pre><code>Most selled products :
  +------------------+------------+
  | Product name     | Quantity   |
  +------------------+------------+
  | product number 2 | 135        |
  | product number 1 | 1          |
  +------------------+------------+
  </code></pre>
  
  <p>I'm facing two problems :</p>
  
  <ul>
  <li>I only know how to retrieve objects, so I could display the product's name, but not the quantity selled, as it's a computed value.</li>
  <li>I don't know how to query my database to retrieve the right informations</li>
  </ul>
  
  <p>This is what I've tried :</p>
  
  <pre><code>public List&lt;Product&gt; getMostSelledProducts() {
      Query q = em.createQuery("SELECT p FROM Sale s RIGHT JOIN Product p GROUP BY s.productId ORDER BY COUNT(s.productId)");
      return q.getResultList();
  }
  </code></pre>
  
  <p>How should I do such a thing?</p>