MySQL how to sum subgroup first then sum total
- by Sunry
My data in table_1 and table_2.
table_1
id_1 num ids_2
3 33 666,777,888
4 44 111,222,333
table_2
id_2 num
111 1
222 2
333 3
666 6
777 7
888 8
I only know how to do what I want with two steps:
First LEFT JOIN to get:
SELECT 1.id_1, sum(2.num) FROM table_1 AS 1 LEFT JOIN table_2 AS 2 on FIND_IN_SET(2.id_2, 1.ids_2) GROUP BY 1.id_1;
id_1 sum(2.num)
3 6+7+8
4 1+2+3
Then LEFT JOIN with table_1 again to sum(table_1.num+sum(2.num)):
id_1 sum(table_1.num+sum(table_2.num))
3 6+7+8+33
4 1+2+3+44
Can I do it in only one SQL?
