title: Count(distinct) and group by issue if the range of items being grouped is significant
body: <p>I am joining two tables (shipments and returns) and using group by to view totals for certain criteria. The two tables are related via shipment_id. this column is mostly unique, but contains a few duplicates because each shipment can contain more than one item that is also contained in the table.</p>
  
  <p>I'm trying to count all the distinct shipments grouped by warehouse, seller, and size. count(distinct works great, but does not report correct information when used with group by if the range of items being grouped is significant.</p>
  
  <p>The query below returns 7 shipments (added up) 4 returns (also added). While with the small amount of test data I have the return count is correct, there are in actuality 6 distinct shipments, not 7. With this query i'm basically looking at all shipments and joining return information if an item in the shipment has been returned.</p>
  
  <pre><code>select s.warehouse, s.seller, s.size,
  count(distinct s.shipment_id) as total_shipments,
  count(distinct r.shipment_id) as total_returns
  from shipments s
  left join returns r
  on s.shipment_id = r.shipment_id
  group by s.warehouse, s.seller, s.size
  </code></pre>
  
  <p>I'm concerned that the report I generate won't be entirely accurate. Is there a work around for this issue? I've seen similar issues, but none that really apply. I am using MYSQL</p>