Divide the sum of one column by the sum of another column when they are from different tables

SQL Server Administration
1

Hello community!

I want to divide the sum of one column by the sum of another column when they are from different tables in SQL.

Practically, I want to divide this result:

SELECT "Tenant Name",
SUM(Amount)
FROM Receipts r
GROUP BY "Tenant Name"
HAVING Segment = "Market"

by

SELECT "Tenant Name",
SUM(Charge3)
FROM Charges r
GROUP BY "Tenant Name"
HAVING Segment = "Market"

But when I use JOIN to make the division, the result is not what I expected. For example, by running each query individually, I get that "Tenant A" Receipt amount (first query) is 2,350 and Charge amount (second query) is 2395, so, if I divide 2350 by 2395 the result is 0.9812 which is the result I expect, but it won't work if I do it in SQL in any way.

What am I doing wrong?

Thanks.

2 

SELECT
    ISNULL(r."Tenant_Name", c."Tenant_Name") AS "Tenant_Name",
    r.Total_Amount,
    c.Total_Charge
FROM (
    SELECT "Tenant Name",
        SUM(Amount) AS Total_Amount
    FROM Receipts r
    WHERE Segment = "Market"
    GROUP BY "Tenant Name"
) AS r
FULL OUTER JOIN (
    SELECT "Tenant Name",
        SUM(Charge3) AS Total_Charge
    FROM Charges c
    WHERE Segment = "Market"
    GROUP BY "Tenant Name"
) AS c ON c."Tenant_Name" = r."Tenant_Name"
1 Like
3

the join has to be on the columns which will uniquely identify

so that
A(receipts)=A(charges)

NOT
A(receipts)= A+B+C(charges)