CODEWITHSUNDEEP
sqlsql-server
Benny Zhang
Benny Zhang

Reputation: 23

How to take sum of column with same id and different table in SQL?

I have 2 following table structure first is like this 

szCustId    szArClassId    decCreditLimit   szPaymentTermId
22101100071    AMORE        0.00            30 HARI_AMORE    
22101100071    BELLISIMO    500.00          17 HARI_BELLISIMO
22101100071    CAPE         0.00            17 HARI_CAPEDISC
22101100071    FOOD         0.00            17 HARI_FOOD
22101100071    GFES         1000.00         0 HARI_GFES
22101100071    GILBEYS      0.00            17 HARI_GILBEYS
22101100071    GZERO        0.00            13 HARI_GZERO
22101100071    AMORE        0.00            30 HARI_AMORE    
22101100069    BELLISIMO    500.00          17 HARI_BELLISIMO
22101100069    CAPE         0.00            17 HARI_CAPEDISC
22101100069    FOOD         0.00            17 HARI_FOOD
22101100888    FOOD         0.00            17 HARI_FOOD
22101100888    GFES         1000.00         0 HARI_GFES
22101100888    GILBEYS      0.00            17 HARI_GILBEYS
22101100888    GZERO        0.00            13 HARI_GZERO

and the second table (that i want to update) is this

szCustId    bAllowToCredit    decCreditLimit    
22101100071 1                   0.00    
22101100069 1                   0.00    
22101100888 1                   0.00

I need to take a sum of decCreditLimit of matching or group by particular szCustId

so the result (in table 2) would be like this

szCustId    bAllowToCredit    decCreditLimit    
22101100071 1                   1500.00 
22101100069 1                   500.00  
22101100888 1                   1000.00

How can I write this SQL query?

Upvotes: 2

Views: 1969

Answers (5)

Shahzad Riaz
Shahzad Riaz

Reputation: 354

Firstly Calculate column sum from first table and then update on seconde table like this. Update t2 Set t2.decCreditLimit = t1.Sum_Column From Table2 t2 ( Select szCustId,Sum(t1.decCreditLimit) as Sum_Column From Table1 t1 group by t1.szCustId )A where A.szCustId = t2.szCustId

Upvotes: 0

Abhishek
Abhishek

Reputation: 2490

A simple group by will do the task - 

SELECT t2.szCustId
     , t2.bAllowToCredit
     , sum(t1.decCreditLimit)
FROM table1 t1
INNER JOIN table2 t2 ON t1.szCustId = t2.szCustId
GROUP BY t2.szCustId
       , t2.bAllowToCredit

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460158

UPDATE t2 SET t2.decCreditLimit = (SELECT SUM(decCreditLimit) 
                                   FROM Table1 t1
                                   WHERE t1.szCustId = t2.szCustId)
FROM Table2 t2

Upvotes: 0

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Try this query

select a.szCustId,a.bAllowToCredit,b.decCreditLimit  from tablesecond a
join(
select szCustId,sum(decCreditLimit) as decCreditLimit  from tablefirst)b
on a.szCustId=b.szCustId

Upvotes: 0

potashin
potashin

Reputation: 44581

You can join two tables and use sum aggregate function with the group by clause to calculate total decCreditLimit for each szCustId and bAllowToCredit:

select t2.szCustId
     , t2.bAllowToCredit
     , sum(t1.decCreditLimit) as decCreditLimit 
from table1 t1
join table2 t2 on t1.szCustId = t2.szCustId
group by t2.szCustId
       , t2.bAllowToCredit

Upvotes: 1

Related Questions