MySQL SELECT Subquery -- WHERE value IN collapsed group-by values

Question

Underlying data:

mysql> select * from parent;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Joe        | Wilkenson |
|  2 | Bob        | Thompson  |
|  3 | Bob        | Ericson   |
+----+------------+-----------+

mysql> select * from child;
+----+-----------+------------+-----------+---------+
| id | parent_id | first_name | last_name | fingers |
+----+-----------+------------+-----------+---------+
|  1 |         1 | Carla      | Wilkenson |      10 |
|  2 |         1 | Peter      | Wilkenson |       8 |
|  3 |         2 | Sam        | Thompson  |      10 |
|  4 |         3 | Beth       | Ericson   |      10 |
|  5 |         3 | Georgie    | Ericson   |      10 |
|  6 |         3 | Kyle       | Ericson   |       7 |
+----+-----------+------------+-----------+---------+

Goal: Figure out the total number of children's fingers, grouped by the parent's first name.

SELECT
  parent.first_name,
  ( SELECT SUM(fingers) FROM child WHERE child.parent_id IN ([collapsed parent.ids]) ) AS `kid_fingers`
FROM parent
GROUP BY parent.first_name;

I know that, with this specific example, the goal can be achieved by simply moving the subquery into a JOIN with a SUM(child.fingers). My actual scenario is far more complicated with a lot more table joins that would result in SUM() returning a very inflated and inaccurate number due to row duplication.

I also attempted to JOIN against a subquery but that's resulting in an unavoidable full scan on a large table which isn't feasible in my environment.

That leaves a SELECT subquery but there's the obvious problem that the field I need to compare against gets collapsed by the GROUP BY. This seems like a really good opportunity for MySQL to offer an aggregate function that simply brings up collapsed values for comparison, but I don't think one exists?

Any other tricks that might work for this? Worst case scenario I can do a REGEXP compare against GROUP_CONCAT() but that seems gross (and potentially risky) for many reasons. I'm unfortunately dealing with a very large and dynamically-built query that can have a lot of different GROUP BY combinations, so a full refactor or redesign is problematic.

P.S. Don't ask why the children are missing fingers. I just needed something to count.

Answer 1

You don't need IN, just use WHERE child.parent_id = parent.id.

Since that's summing for each parent ID, you can wrap it in another SUM() to combine all the parents with the same first name in the outer query.

SELECT
  parent.first_name,
  SUM(( SELECT SUM(fingers) FROM child WHERE child.parent_id = parent.id )) AS `kid_fingers`
FROM parent
GROUP BY parent.first_name;

DEMO

Another option is to use another nested subquery to get all the parent IDs with the same name, and sum that (I guess this is what you meant by "collapsed parent.ids").

SELECT
  parent.first_name,
  ( SELECT SUM(fingers) 
    FROM child 
    WHERE child.parent_id IN (
        SELECT p.id 
        FROM parent AS p 
        WHERE p.first_name = parent.first_name
    )
  ) AS `kid_fingers`
FROM parent
GROUP BY parent.first_name;

DEMO

And of course it can be done with a join:

SELECT p.first_name, SUM(c.kid_fingers) AS kid_fingers
FROM parent AS p
JOIN (
  SELECT parent_id, SUM(fingers) AS kid_fingers
  FROM child
  GROUP BY parent_id
) AS c ON c.parent_id = p.id
GROUP BY p.first_name

DEMO