CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008
user3418716
user3418716

Reputation: 41

multiple sum using join in sql

This is Rudresh BR, Trying to obtain data of same rows into multiple columns also by doing sum. But stuck with an issue, Please find the below mentioned details regarding the issue,

Expected Data:

Expected Data

But obtained Data:

But obtained Data

Data Existing in Table

  1. List item

Actual Data in Table

Query used:

select 
    a.BM_BANK_NAME,
    SUM(PCBunit.BID_CURRENCY_VALUE) as PCBUnitSum,
     SUM(PTBunit.BID_CURRENCY_VALUE) as PTBUnitSum  
from 
     dbo.BG_Mtr_Bank_Master a 
     inner join dbo.BG_Tra_Issuance_Details PCBunit on a.BM_ID=PCBunit.BID_BANK_NAME and PCBunit.BID_UNIT_DIVISION='PCB' 
     inner join dbo.BG_Tra_Issuance_Details PTBunit on a.BM_ID=PTBunit.BID_BANK_NAME and PTBunit.BID_UNIT_DIVISION='PTB' 
group by 
     a.BM_BANK_NAME,
     PCBunit.BID_UNIT_DIVISION,
     PTBunit.BID_UNIT_DIVISION

PCBUnitSum and PTBUnitSum is the sum of rows of BID_Currency_Value of PCB and PTB respectively.

What I am observing is once the sum is done i.e 3000(which i am expecting as output) , it's redoing the sum based on number of rows PCB and PTB exists respectively, so as there are 3 rows of PCB, 3000+3000+3000=9000 is given as O/P.

I request everyone to, Please help me out to find what's going wrong?

Upvotes: 1

Views: 53

Answers (2)

Dheeraj Sharma
Dheeraj Sharma

Reputation: 709

Try this:

DECLARE @list_item TABLE ( unit NVARCHAR(5),bank NVARCHAR(5),curr INT)
INSERT INTO @list_item VALUES ('PCB','SBI',1000),('PCB','SBI',1000),
('PCB','SBI',1000),('PTB','SBI',1000),('PTB','SBI',1000),
('PTB','SBI',1000)

SELECT bank,
SUM(CASE WHEN unit='PCB' THEN curr END) PCBUnitSum,
SUM(CASE WHEN unit='PTB' THEN curr END) PTBUnitSum
FROM ( SELECT unit,bank,SUM(curr)curr FROM @list_item 
group by unit,bank ) A GROUP BY bank

This might help. :)

Upvotes: 0

Serg
Serg

Reputation: 22811

Do not self-join the table, use case 

select a.BM_BANK_NAME
     , SUM(case when t.BID_UNIT_DIVISION ='PCB' then t.BID_CURRENCY_VALUE end) as PCBUnitSum
     , SUM(case when t.BID_UNIT_DIVISION ='PTB' then t.BID_CURRENCY_VALUE end) as PTBUnitSum  
  from dbo.BG_Mtr_Bank_Master a 
 inner 
  join dbo.BG_Tra_Issuance_Details t 
    on a.BM_ID = t.BID_BANK_NAME 
   and t.BID_UNIT_DIVISION in ('PCB', 'PTB')
 group  
    by a.BM_BANK_NAME

Upvotes: 2

Related Questions