MySQL how to sum subgroup first then sum total

Posted by Sunry on Stack Overflow See other posts from Stack Overflow or by Sunry
Published on 2013-10-24T09:52:14Z Indexed on 2013/10/24 9:53 UTC
Read the original article Hit count: 289

Filed under:
|

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?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about sql