CODEWITHSUNDEEP
mysqlsqlgroup-by
Sumit
Sumit

Reputation: 2426

How to sum rows from a child table grouped by a column in parent table

I have two tables in MySQL:

Parent:

parent {
  id: int,
  name: string,
  group: string,
}

Child:

child {
  id: int,
  parent_id: int,
  value: int,
  value_date: date
}

Here is some sample data:

parent:

id | name | group
1 | A | G1
2 | B | G2
3 | C | G3
4 | D | G2
5 | E | G3
6 | F | G3

child:

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

I want to find the sum of the values in value column of child table, grouped by the group of parent table and the value_date column of the child table. How to do that in single query?

Here is what I want in result:

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

Basically, it is a grouping of the child values based on parent group and child value dates.

Right now I am doing this:

First getting all the distinct Groups like this:

SELECT DISTINCT group FROM parent;

This will give me following values: ('G1', 'G2', 'G3')

And then I call the below query once for each distinct group value from the above result from my Java Code:

SELECT SUM(value), value_date FROM child WHERE parent_id IN (SELECT id FROM parent WHERE group = ?) group by value_date;

Java:

List<String> groups = getGroups();    

for (String group : groups) {
    Map<String, Integer> dateValueMap = getValue(group);
}

Is there a way to do this in one query?

Upvotes: 1

Views: 2746

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

I do hope your column name isn't group as it is a SQL term and reserved word

SELECT
    parent.`group`
  , child.value_date
  , SUM(child.value)
FROM parent
INNER JOIN child ON child.parent_id = parent.id
GROUP BY
    parent.`group`
  , child.value_date

https://dev.mysql.com/doc/refman/5.7/en/keywords.html#keywords-5-7-detailed-G

Upvotes: 3

MatBailie
MatBailie

Reputation: 86715

SELECT
  parent.group,
  child.value_date,
  SUM(child.value)
FROM
  parent
INNER JOIN
  child
    ON child.parent_id = parent.id
GROUP BY
  parent.group,
  child.value_date

Upvotes: 1

Related Questions