How can I use SUM and COUNT in the same query with mysql and get accurate results

Question

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.

I'm trying to use a query like this:

   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

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.

Any help would be greatly appreciated.

EDIT: The output should be something like this:

DATE | ORDER COUNT | GRAND TOTAL

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.

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

Solution

I'd use a subquery to first sum over the values of the subtable like

SELECT t1.id, t1.order_total, SUM(t2.product_price) product_total
FROM t1 LEFT JOIN t2 on t1.id=t2.id 
GROUP BY t1.id, t1.order_total

this query returns all single orders with their summed product price.

The make the outer query summing over order_total and returning the count like

SELECT COUNT(DISTINCT(t1.id)) order_count,
       SUM(t1.order_total) order_total,
       SUM(t2.product_price) product_total
FROM (
  SELECT t1.order_date, t1.id, t1.order_total, SUM(t2.product_price) product_total
  FROM t1 LEFT JOIN t2 on t1.id=t2.id 
  GROUP BY t1.order_date, t1.id, t1.order_total
) GROUP BY t1.order_date

No garantuee that this code actually works, my SQL's a bit rusty... But I hope you got the idea.

EDIT (in response to your edits...)

The IF-construct is misplaced in your code: Either you use something like SUM(IF(op.record_type='cpn',op.price,0)) or, even better, place a WHERE clause in your inner query selecting only OPs with record_type='cpn', i.e. make it

....
SUM(op.price) coupon_total
FROM orders o
LEFT JOIN orders_products op ON o.order_number=op.order_number
WHERE op.record_type='cpn'` 
GROUP BY o.order_number
....

Comments

[Admin]

I might be able to implement this but I'm hesitant to use it because I don't understand how it works and what exactly it's doing and how to alter/fix it in the future if something goes wrong with it.

[MartinStettner]

Think of the first (sub-) query as an intermediate table which contains all orders and their respective product totals (i.e. summing only over t2). The second (outer) query then uses this and calculates the remaining sums/counts. Since there is only one line for each order in the subquery, the sum over order_total should be done correctly.

[Admin]

I've implemented a query similar to yours. The results are almost accurate. Edits in my original question explain more.