title: How to write a SQL query to generate a report in mysql
body: <p>I need to generate a report like the below table   </p>
  
  <pre><code>Collection       |Model|Units Sold|Price USD |Price Euro|Price gbp
  -----------------+-----+----------+----------+----------+----------
  Pays de la Loire | 1301|         2|      1000|        600|      400
  Toscana          | 1301|         1|       500|        300|      200
  Provence         | 1302|         1|       400|        400|      400
  </code></pre>
  
  <p>Below are the 3 tables from where this above i need to generate</p>
  
  <p>order Table</p>
  
  <pre><code>ID|collection_fk|model_fk
  --+-----------+---------
   1|          1|        1
   2|          1|        1
   3|          2|        1
   4|          3|        2
  </code></pre>
  
  <p>Collection Table</p>
  
  <pre><code>ID|Collection
  --+-----------
   1| Pays de la Loire
   2| Toscana
   3| Provence
   4|  Lorraine
  </code></pre>
  
  <p>Models Table</p>
  
  <pre><code>ID|model_no   |collection_fk|price_usd|price_euro|price_gbp
  --+-----------+-------------+---------+----------+---------
   1|       1301|            1|     500|        300|      200
   2|       1302|            1|     400|        400|      400
   3|       1303|            2|     300|        200|      500
   4|       1304|            3|     200|        100|      300
  </code></pre>
  
  <p>How would i write a query to get sum of same collection and models and count number of units sold an count their price accordingly.</p>