How to create a table with multiple calculations?-CodePudding

If I do one calculation with one join:

SELECT
    SUM(friends_made) as calc1, table2.group_id
FROM
    friends_made_table as table1
INNER JOIN
    grouped_users as table2 ON table1.user_id = table2.user_id
GROUP BY 
    table2.group_id

The result I get is:

calc1 | group_id
-----------------
400   |   1
320   |   2
330   |   3

But I also need another calculation (calc2) with the same inner join on table1 but with a different table (table3)

SELECT
    SUM(request_accept) AS calc2, table1.group_id
FROM
    friends_accept_table AS table3
INNER JOIN
    grouped_users as table1 ON table1.user_id = table3.user_id
GROUP BY
    table1.group_id

Result is:

calc2 | group_id
-----------------
100   |   1
150   |   2
120   |   3

How can I join these two queries and create a new table showing both of the calculations (calc1, calc2)?

calc1 |calc2 | group_id
-----------------------
400   | 100  | 1
320   | 150. | 2
330   | 120. | 3

EDITED to show tables/results and take out rounding

CodePudding user response：

A join will suffice as long as there is a common set of group_ids across the two results. You may otherwise need a left/right join or full join.

with data1 as (
    SELECT SUM(friends_made) as calc1, table2.group_id
    FROM friends_made_table as table1 INNER JOIN grouped_users as table2
        ON table1.user_id = table2.user_id
    GROUP BY table2.group_id
), data2 as (
    SELECT SUM(request_accept) as calc2, table1.group_id
    FROM friends_accept_table as table3 INNER JOIN grouped_users as table1
        ON table1.user_id = table3.user_id
    GROUP BY table1.group_id
)
select calc1, calc2, d1.group_id
from data1 d1 inner join data2 d2 on d2.group_id = d1.group_id;

This does assume that your platform supports CTE syntax. If it doesn't there are probably similar rewrites.

CodePudding user response：

I think using both tables in 1 query and joining them should work -

SELECT SUM(friends_made) as calc1, 
       SUM(request_accept) as calc2,
       GU.group_id
  FROM grouped_users as GU
       INNER JOIN friends_made_table as FMT ON FMT.user_id = GU.user_id
       INNER JOIN friends_accept_table as FAT ON FAT.user_id = GU.user_id
 GROUP BY table2.group_id;