title: Mysql Sum one row only once - with Group By
body: <p>I have a seemingly simple problem that I can't seem to solve.  Imagine an <code>order</code> table with a shipping value for that order.  And imagine an <code>item</code> table that point to the order, and have a price:</p>
  
  <p><strong>Order</strong></p>
  
  <pre><code> |id |created   |shipping|
   |---|----------|--------|
   |101|2018-01-01|10      |
   |102|2018-01-01|10      |
  </code></pre>
  
  <p><strong>Item</strong></p>
  
  <pre><code> |order_id|price   |
   |--------|--------|
   |101     |1       |
   |101     |2       |
   |102     |3       |
  </code></pre>
  
  <p>So there were two orders.  Each had a shipping fee of $10.  One order had two items, priced $1 and $2. The other had one item priced $3.  Very simple so far.</p>
  
  <p>I want to write a query, that will find the total shipping price and the total item price (summed) for every day (group by <code>created</code>).  Naively, I would write this query:</p>
  
  <pre><code>SELECT 
      o.created, sum(o.shipping), sum(i.price)
  FROM
      test_order o,
      test_item i
  WHERE
      i.order_id = o.id
  group by o.created
  </code></pre>
  
  <p>This query is very simple.  It joins the two tables, and then <code>group by</code> that date, and sums vertically.  The problem is with the shipping part.  Since there are three rows after the join, there are three $10 shipping fees in the sum, which is entirely incorrect.</p>
  
  <p>I could add a <code>distinct</code> to my shipping clause (<code>sum(distinct o.shipping)</code>), but that, too, is incorrect. It would evaluate those three 10s, and only keep one, resulting in a summed value of $10.  It doesn't recognize that two of those 10s came from one source row, while the third 10 comes from an entirely different source row, and is desirable to include it in the sum.</p>
  
  <p>My <em>real</em> query is much more complicated that this, but I distilled it down to this.  I would think this is a common situation for many use cases.  Of course, I could write a second query (or a sub query), but I'd like to not do that if at all possible.</p>
  
  <p>Wanna play with this data yourself? Here is the sql:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `test_item` (
   `order_id` int(11) NOT NULL,
   `price` int(11) NOT NULL)
  INSERT INTO `test_item` (`order_id`, `price`) VALUES (101, 1), (101, 2), (102, 3);
  
  CREATE TABLE IF NOT EXISTS `test_order` (
   `id` int(12) NOT NULL,
   `created` datetime NOT NULL,
   `shipping` int(11) NOT NULL,
   PRIMARY KEY (`id`)
  )
  INSERT INTO `test_order` (`id`, `created`, `shipping`) VALUES (101, '2018-01-01 00:00:00', 10), (102, '2018-01-01 00:00:00', 10);
  </code></pre>
  
  <p>Thanks!</p>