title: in MySQL Query how to use count and sum in master-detail tables
body: <p>I'm looking for a way to produce something like this that groups by each month (and year)  for a given customer all their invoices (count) and a (sum) of the details qty_shipped. </p>
  
  <p>(example output)</p>
  
  <pre><code>CUSTOMER_NO , YR,    MO,   COUNT_INVOICES, TOTAL_QTY_SHIPPED
  18            2015,  01,   30,             300 
  18            2014,  12,   65,             12323
  18            2014,  11,   34,             4344
  </code></pre>
  
  <p>I have a simple invoices (master pk:inv_id) table and corresponding invoices-detail (detail fk_inv_id), the detail table is joined to the master by the fk: inv_id. Table structure looks like. </p>
  
  <pre><code>MASTER (Invoice)
  inv_id
  innvoice_number
  customer_no
  updated
  
  DETAIL (invoices-detail )
  invdet_id
  inv_id
  product_no
  qty_shipped
  qty_ordered
  
  
  ...
  </code></pre>
  
  <p>I'm trying but it doesn't seem to work.It seems to mis-count the sum of the qty_shipped</p>
  
  <pre><code> SELECT customer_no,YEAR(i.updated) as yr, MONTH(i.updated) as mnth,count(i.inv_id) as  invoices_shipped ,
  --subquery--
  (SELECT  count(idet.qty_shipped)  
  FROM invoice_details idet WHERE idet.inv_id=i.inv_id) as qty_shipped_TOTAL 
  --end subquery--
  FROM invoice i WHERE status="SHIPPED" AND  customer_no=18 
  GROUP BY  YEAR(i.updated) DESC, MONTH(i.updated) DESC;
  </code></pre>
  
  <p>Any suggestions?</p>