No, I think I missed something -- the GROUP BY (outer query) invalidates the pattern I presented. Sorry.
Another pattern:
1. Each inner query gathers subtotal, something like
SELECT user,
color,
piece,
SUM(quantity) AS sum_quantity
FROM test1_loose
GROUP BY user, color, piece
ORDER BY user, color, piece -- let's be clearer about order
LIMIT 0,50
You should explicitly say UNION ALL, else you will lose some values.
2. Then the outer query sums the sums:
SELECT user, color, piece,
SUM(sum_quantity) AS quantity -- summing the sums
FROM (... UNION ALL ...) -- note the ALL
GROUP BY user, color, piece
ORDER BY user, color, piece -- suggest you match the GROUP BY
LIMIT 0,50
I think (hope) this will safely get past your questions.
Puzzle: You are getting the first 50 rows; then what? Pagination? With the possibility of a user split between pages?
If one of the subselects has missing users/colors/pieces, there should be no problem; the first 50 will overlay, and some will be tossed.