title: How can I use SUM and COUNT in the same query with mysql and get accurate results
body: <p>I have two tables in a one to many relationship. More specifically, t1 is order information and t2 is line item details on those orders.</p>
  
  <p>I'm trying to use a query like this:</p>
  
  <pre><code>   SELECT COUNT(DISTINCT(t1.id)) order_count,
            SUM(t1.order_total) order_total,
            SUM(t2.product_price) product_total,
            DATE(t1.order_date) order_date
       FROM t1
  LEFT JOIN t2 ON t1.id = t2.id
   GROUP BY t1.order_date
  </code></pre>
  
  <p>The query returns the correct value for order_count. However the other values are inflated incorrectly. I understand that with the left join I'm adding extra rows and that's why the sum's are incorrect. I'm just not sure how to fix it.</p>
  
  <p>Any help would be greatly appreciated.</p>
  
  <p>EDIT: The output should be something like this:</p>
  
  <p>DATE | ORDER COUNT | GRAND TOTAL</p>
  
  <p>I developed the query below based on a response. It returns all values correctly except for the coupon_total which it returns as 0 every time. </p>
  
  <pre><code>SELECT
  COUNT(DISTINCT(o.order_number)) order_count,
  DATE(o.order_date) order_date,
  SUM(o.total_product_total) product_total,
  SUM(o.total_shipping) shipping_total,
  SUM(o.total_grand_total) grand_total,
  o.coupon_total
  FROM (
   SELECT
   DATE(o.order_date) order_date,
   o.order_number,
   o.total_product_total,
   o.total_shipping,
   o.total_grand_total,
   IF(op.record_type='cpn',SUM(op.price),0) coupon_total
   FROM orders o
   LEFT JOIN orders_products op ON o.order_number=op.order_number
   GROUP BY o.order_number
  ) o
  GROUP BY DATE(o.order_date)
  ORDER BY o.order_date DESC
  </code></pre>