(mysql) how can i get sum of sum?

Question

(select sum(serreq_totalamount)
                from servicerequirement
                    group by key_id)+
            (select sum(facreq_totalamount)
                from facilityrequirement
                    group by key_id)+
            (select sum(resorder_totalamount)
                from restaurantorder
                    group by key_id)

I want to get sum of sum. As I want above.

How do I create a column that adds up the sum column of these three tables?

First of all, 3 was too difficult, so I tried 2 tables, but I got an error.

select (
(select sum(serreq_totalamount)
        from servicerequirement
            group by servicerequirement.key_id)+
(select sum(facreq_totalamount)
        from facilityrequirement
            group by facilityrequirement.key_id));

Error Code: 1242. Subquery returns more than 1 row  0.000 sec

The same is true with join.

(select
(select sum(serreq_totalamount)
        from servicerequirement
            group by servicerequirement.key_id),
    (select sum(facreq_totalamount)
        from facilityrequirement
            group by facilityrequirement.key_id)
        from servicerequirement
    inner join facilityrequirement
    on servicerequirement.key_id=facilityrequirement.key_id);

Error Code: 1242. Subquery returns more than 1 row  0.000 sec

I'll be happy if someone helps me.

Answer 1

You must make suqueries and join them by key_id

Then you can add them up

SELECT t1.key_id,s1+s2+s3
FROM
(select key_id,sum(serreq_totalamount) s1
                from servicerequirement
                    group by key_id) t1
INNER JOIN 
            (select key_id,sum(facreq_totalamount) s2
                from facilityrequirement
                    group by key_id) t2 ON t1.key_id = t2.key_id
INNER JOIN
            (select key_id,sum(resorder_totalamount)
                from restaurantorder
                    group by key_id) t3 ON t2.key_id = t3.key_id