title: SUM calculating duplicate records in MySQL join
body: <p>I'm having problems with a join in MySQL. I have 2 tables, customerorder and customerorderpos. The pos table contains the items ordered - so there is always just one entry per order in the customerorder table but there may be several with the same customerorderid in the customerorderpos.</p>
  
  <p>Everything works in the query except when I try to do a sum calculation on customerorder. For example sum(cart_total_complete). This is returning duplicate values (it's counting more than once for the orders with multiple customerorderpos records). </p>
  
  <p>I'm sure it's something basic, either with the join type or how I use distinct, but I've been trying for hours and just can't get it to work...</p>
  
  <p>Any ideas what I'm doing wrong?
  Thanks for your help!!</p>
  
  <pre><code>select concat(left(dayname(from_unixtime(customerorder.datetime)),3), ' ',
  day(from_unixtime(customerorder.datetime))) as day, 
  count(distinct customerorder.customerorderid) as count_totalorders, 
  count(distinct customerorderpos.itemid) as count_differentitems, 
  sum(customerorderpos.quantity_ordered - customerorderpos.quantity_cancelled) as quantity_ordered, 
  sum(customerorderpos.itemsubtotal) as item_subtotal, 
  sum(customerorderpos.pricechangetotal) as item_pricechangetotal, 
  sum(customerorderpos.itemtotal) as item_total, 
  sum(customerorderpos.purchase_price * (customerorderpos.quantity_ordered - customerorderpos.quantity_cancelled)) as item_purchasepricetotal, 
  sum(cart_discounttotal) as total_discount, 
  sum(customerorderpos.itemtotal) - sum(customerorderpos.purchase_price * (customerorderpos.quantity_ordered - customerorderpos.quantity_cancelled)) - sum(cart_discounttotal) as item_earningstotal, 
  sum(cart_total_shipping) as total_shipping, 
  sum(cart_total_tax) as total_tax, 
  sum(cart_total_complete) as total_complete 
  from customerorder inner join customerorderpos on customerorderpos.customerorderid = customerorder.customerorderid 
  where customerorder.status_cancelled = 0 
  group by day(from_unixtime(customerorder.datetime)) order by customerorder.datetime
  </code></pre>