title: How to sum rows from a child table grouped by a column in parent table
body: <p>I have two tables in MySQL:</p>
  
  <p><strong>Parent</strong>:</p>
  
  <pre><code>parent {
    id: int,
    name: string,
    group: string,
  }
  </code></pre>
  
  <p><strong>Child</strong>:</p>
  
  <pre><code>child {
    id: int,
    parent_id: int,
    value: int,
    value_date: date
  }
  </code></pre>
  
  <p>Here is some sample data:</p>
  
  <p><strong>parent</strong>:</p>
  
  <pre><code>id | name | group
  1 | A | G1
  2 | B | G2
  3 | C | G3
  4 | D | G2
  5 | E | G3
  6 | F | G3
  </code></pre>
  
  <p><strong>child</strong>:</p>
  
  <pre><code>id | parent_id | value | value_date
  1 | 1 | 50 | 2018-09-17
  2 | 2 | 10 | 2018-09-17
  3 | 4 | 20 | 2018-09-17
  4 | 2 | 60 | 2018-09-18
  5 | 3 | 20 | 2018-09-17
  6 | 5 | 30 | 2018-09-17
  7 | 6 | 50 | 2018-09-17
  8 | 5 | 20 | 2018-09-18
  9 | 1 | 30 | 2018-09-18
  10 | 3 | 30 | 2018-09-18
  </code></pre>
  
  <p>I want to find the sum of the  values in <code>value</code> column of child table, grouped by the <code>group</code> of parent table and the <code>value_date</code> column of the child table. How to do that in single query?</p>
  
  <p>Here is what I want in result:</p>
  
  <pre><code>group | sum(value) | value_date
  G1 | 50 | 2018-09-17
  G1 | 30 | 2018-09-18
  G2 | 30 | 2018-09-17
  G2 | 60 | 2018-09-18
  G3 | 100 | 2018-09-17
  G3 | 50 | 2018-09-18
  </code></pre>
  
  <p>Basically, it is a grouping of the child values based on parent group and child value dates.</p>
  
  <p>Right now I am doing this:</p>
  
  <p>First getting all the distinct Groups like this:</p>
  
  <pre><code>SELECT DISTINCT group FROM parent;
  </code></pre>
  
  <p>This will give me following values: ('G1', 'G2', 'G3')</p>
  
  <p>And then I call the below query once for each distinct group value from the above result from my Java Code:</p>
  
  <pre><code>SELECT SUM(value), value_date FROM child WHERE parent_id IN (SELECT id FROM parent WHERE group = ?) group by value_date;
  </code></pre>
  
  <p><strong>Java</strong>:</p>
  
  <pre><code>List&lt;String&gt; groups = getGroups();    
  
  for (String group : groups) {
      Map&lt;String, Integer&gt; dateValueMap = getValue(group);
  }
  </code></pre>
  
  <p>Is there a way to do this in one query?</p>