SQL SUM on multiple INNER JOIN

Question

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.

jarlh · Accepted Answer · 2018-08-29 07:25:12Z

4

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.)

answered Aug 29, 2018 at 7:25

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Q2x13 Over a year ago

Hi @jarlh, I actually wanted something like this. Thanks, I tried this and it works, but the response is very slow. For a single state it takes 36 secs where as the method that i shared gets the result in 1 second. I think it is because the loan and balance tables are huge and it takes time to group them and then the sum. I tried adding filters in the inner query but apparently it does not recognise the fields of State table. I'll try to store the state in a variable and pass it to the inner query. Maybe it will work.

Thorsten Kettner Over a year ago

@Pradumn Kumar Mahanta: This mehod shown here by jarlh should be the fastest possible. Nonetheless, as you want to select all existing data, this takes time. You may want to create two covering indexes on loan(state, loan) and balance(state, balance), so the DBMS finds all loans and balances already separated by state.

Thorsten Kettner Over a year ago

@Pradumn Kumar Mahanta: And if you need this still faster, you can decide to store the data in table partitions. See learn.microsoft.com/en-us/sql/relational-databases/partitions/… and cathrinewilhelmsen.net/2015/04/12/…

D-Shih · Accepted Answer · 2018-08-29 07:30:14Z

4

You can try this, use UNION ALL combine Balance and Loan tables and use a little skill,

Balance fill a Loan column set 0
Loan fill a Balance column set 0

then JOIN with State do SUM

create table State(
  STATE varchar(50)
);



insert into State values ('FL'); 
insert into State values ('CA'); 
create table Balance(
  STATE varchar(50),
  Balance int
);



insert into Balance values ('FL',100);
insert into Balance values ('FL',200);
insert into Balance values ('CA',300);
insert into Balance values ('CA',200);
insert into Balance values ('CA',100);

create table Loan(
  STATE varchar(50),
  loan int
);

insert into loan values ( 'FL',500);
insert into loan values ( 'FL',600);
insert into loan values ( 'CA',700);
insert into loan values ( 'CA',100);
insert into loan values ( 'CA',200);

Query 1:

SELECT s.STATE,SUM(t1.Loan) as 'Loan',SUM(t1.Balance) as 'Balance'
FROM 
(
  SELECT STATE,0 AS Loan,Balance  
  FROM Balance 
  UNION ALL
  SELECT STATE,Loan,0   
  FROM Loan 
) t1
INNER JOIN State s on s.STATE = t1.STATE
GROUP BY s.STATE

Results:

| STATE | Loan | Balance |
|-------|------|---------|
|    CA | 1000 |     600 |
|    FL | 1100 |     300 |

edited Aug 29, 2018 at 7:30

answered Aug 29, 2018 at 7:21

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

1 Comment

Thorsten Kettner Over a year ago

That's a good approach. Single quotes, however, are delimiters for string literals, not for names. Standard SQL uses double quotes, SQL Server uses brackets, but you don't need them anyway: SUM(t1.Loan) as Loan, SUM(t1.Balance) as Balance.

codeLover · Accepted Answer · 2018-08-29 07:26:07Z

0

Can you try this:

Select S.State, B.Balance,L.Loan from State S 
inner join (select state, sum(loan) as Loan from Loan group by state) L
inner join (select state, sum(balance) as Balance from Balance group by state) B
on L.state=S.state and B.State=S.State;

answered Aug 29, 2018 at 7:26

codeLover

2,5921 gold badge13 silver badges29 bronze badges

1 Comment

jarlh Over a year ago

Doesn't each INNER JOIN need an own ON clause, for SQL Server?

Fahmi · Accepted Answer · 2018-08-29 07:28:02Z

0

Use subquery: first join loan and balance table and then join state table with that derived table result

SELECT 
  S.STATE AS STATE, Loan,Balance
FROM State AS S inner join
 (select L.state,sum(loan) as loan, sum(balance) as balance
 from Loan AS L INNER JOIN Balance AS B ON B.STATE = S.STATE
 GROUP BY L.STATE)X on s.state=x.state

answered Aug 29, 2018 at 7:28

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Comments

xLarry · Accepted Answer · 2018-08-29 07:43:51Z

0

I'd recommend to use a Common Table Expression, as it is quite "clean" to read.

Try something like the following:

WITH CTE_TotalLoan AS (
  SELECT
    State,
    SUM(Loan) AS TotalLoan
  FROM Loan
  GROUP BY State
),

CTE_TotalBalance AS (
  SELECT
    State,
    SUM(Balance) AS TotalBalance
  FROM Balance
  GROUP BY State
)

SELECT 
  S.State,
  TotalLoan,
  TotalBalance
FROM State AS S
INNER JOIN CTE_TotalLoan AS L ON L.STATE = S.STATE
INNER JOIN CTE_TotalBalance AS B ON B.STATE = S.STATE

See working SQL Fiddle.

answered Aug 29, 2018 at 7:43

xLarry

1811 silver badge4 bronze badges

Comments

TanvirArjel · Accepted Answer · 2018-08-29 08:09:46Z

0

Try this:

SELECT S.STATE, L.Loan as TotalLoan, B.Balance as TotalBalance
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

The result is:

| STATE |TotalLoan|TotalBalance|
----------------------------
|    FL |   1100  |     300    |
|    CA |   1000  |     600    |

edited Aug 29, 2018 at 8:09

answered Aug 29, 2018 at 7:24

TanvirArjel

32.5k14 gold badges89 silver badges120 bronze badges

Comments

Yogesh Sharma · Accepted Answer · 2018-08-29 10:49:31Z

0

You can also use APPLY :

SELECT s.*, l.loan, b.balance 
FROM State s OUTER APPLY
     (SELECT SUM(l.loan) loan
      FROM loan l
      WHERE l.state = s.state
     ) l OUTER APPLY 
     (SELECT SUM(b.balance) balance 
      FROM balance b
      WHERE b.state = s.state
     ) b;

answered Aug 29, 2018 at 10:49

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Comments

bdkopen · Accepted Answer · 2022-05-24 02:19:42Z

try this:

Create Table #Loan([State] Varchar(10),Loan Bigint)
Create Table #Balance([State] Varchar(10),Balance Bigint)

Insert Into #Loan
Select 'FL',500 Union All
Select 'FL',600 Union All
Select 'CA',700 Union All
Select 'CA',100 Union All
Select 'CA',200

Insert Into #Balance
Select 'FL',100 Union All
Select 'FL',200 Union All
Select 'CA',300 Union All
Select 'CA',200 Union All
Select 'CA',100 

;with cteLoan
AS(
    Select ROW_NUMBER() Over(Partition by State order by State) As Rn, * from #Loan
)
,
cteBalance
AS(
    Select ROW_NUMBER() Over(Partition by State order by State) As Rn, * from #Balance
)

Select DISTINCT ISNULL(l.State,b.State) as State
,SUM(ISNULL(Loan,0)) As Loan
,SUM(ISNULL(Balance,0)) As Balance
from cteLoan l
FULL OUTER JOIN cteBalance b
on l.State=b.State and l.rn=b.rn
Group by ISNULL(l.State,b.State)

Drop Table #Loan
Drop Table #Balance

Collectives™ on Stack Overflow

SQL SUM on multiple INNER JOIN

8 Answers 8

3 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

3 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related