I am trying to get the sum of particular fields through multiple table using joins. I am getting the SUM wrong when I I try to get the values together.
I have table State as
| STATE | MONTH | ID | TOTAL |
--------------------------------
| FL | July | 1 | 10000 |
| CA | April | 32 | 2000 |
I have another table Balance as
| STATE | Balance|
-------------------
| FL | 100|
| FL | 200|
| CA | 300|
| CA | 200|
| CA | 100|
I have one more table Loan as
| STATE | Loan|
-------------------
| FL | 500|
| FL | 600|
| CA | 700|
| CA | 100|
| CA | 200|
What I want as a result from my query is,
| STATE | Loan| Balance|
----------------------------
| FL | 1100| 300|
| CA | 1000| 600|
When I try to use the following query I am getting the sum for loan with state correctly,
SELECT
S.STATE,
SUM(L.Loan)
FROM State AS S
INNER JOIN Loan AS L ON L.STATE = S.STATE
GROUP BY
S.STATE
I get the following result,
| STATE | Loan|
--------------------
| FL | 1100|
| CA | 1000|
Similarly I can get the sum from the Balance Table. What I am trying to achieve is to get the sum form both the tables using a single transaction.
Although, I am able to get the desired values, if I do something like this,
SELECT
STATE AS STATE
SUM(DataSetB.Balance) AS Balance
FROM
(
SELECT
STATE AS STATE,
B.Balance AS Balance
FROM
(
SELECT
S.STATE AS STATE,
SUM(L.Loan) AS Loan,
FROM State AS S
INNER JOIN Loan AS L ON L.STATE = S.STATE
GROUP BY S.STATE
)
AS DataSetL
INNER JOIN Balance AS B ON B.STATE = DataSetL.STATE
GROUP BY
DataSetL.STATE, B.Balance
) AS DataSetB
GROUP BY
DataSetB.STATE
However this is not very feasible on the large data set that I have. I tried,
SELECT
S.STATE AS STATE,
SUM(L.Loan) AS Loan,
SUM(B.Balance) AS Balance
FROM State AS S
INNER JOIN Loan AS L ON L.STATE = S.STATE
INNER JOIN Balance AS B ON B.STATE = S.STATE
GROUP BY
S.STATE
But this gives me values as multiple of the actual value. It is actually the multiple of number of rows present in the child tables.
I am not asking for an exact solution, but any kind of pointers would be nice.
Advertisement
Answer
Do the aggregation for each table in a separate sub-query, then join:
SELECT
S.STATE AS STATE,
L.Loan,
B.Balance
FROM State AS S
LEFT JOIN (select state, sum(loan) as Loan
from loan
group by state) AS L ON L.STATE = S.STATE
LEFT JOIN (select state, sum(Balance) as balance
from balance
group by state) AS B ON B.STATE = S.STATE
(LEFT JOINs just in case a state has no loan or balance rows.)