## How to calculate total sum of these table

 Author Message raghuldrag SSC-Enthusiastic Group: General Forum Members Points: 186 Visits: 473 Hi Friends i ve the table like create table accutn_det( fs_locn char(50), fs_accno varchar(100),fs_cost_center varchar(100), fs_tran_type char(50) fs_amount numeric(50), fs_trans_date datetime,)insert into accutn_det values('CHN','E-Sw-2100','205produ','Cr','5000','2014-05-01')values('CHN','E-Sw-2100','205produ','Dr','15000','2014-05-06')values('HYD','E-Sw-2100','206produ','Dr','8000','2014-05-03')values('BANG','E-Sw-2100','208produ','Dr','25000','2014-05-01')values('BANG','E-Sw-2100','208produ','Cr','5000','2014-05-06')like all loctaion details stored from all months in these table here Dr=debit,Cr=Credit Formula= 'Dr-Cr' to find the salary wavges of amountso i made the query to find the amount for mayselect fs_locn, fs_accno, amount=sum(case when fs_accno like 'E%' and fs_tran_type='Dr' then fs_amount when fs_accno like 'E%' and fs_tran_type='Cr' then fs_amount * -1 end )from accutn_det where fs_trans_date between '01-may-2014' and '31-may-2014'groupby fs_locn,fs_accnonow i need the sum values of all costcenter for the particular accounthow to do that? Koen Verbeeck SSCoach Group: General Forum Members Points: 16516 Visits: 13210 Add the cost center in the GROUP BY? How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence Hardy21 Ten Centuries Group: General Forum Members Points: 1204 Visits: 1399 Add Cost center (fs_cost_center) in Group by clause. Thanks raghuldrag SSC-Enthusiastic Group: General Forum Members Points: 186 Visits: 473 Hi Friends i need out put likefor exmaple :account no 205prod 206prod totalE-SW-100 10000 3000 13000how make code? Koen Verbeeck SSCoach Group: General Forum Members Points: 16516 Visits: 13210 raghuldrag (7/11/2014)Hi Friends i need out put likeaccount no 205prod 206prod totalE-SW-100 10000 3000 13000how make code?How is the 3000 calculated? How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence raghuldrag SSC-Enthusiastic Group: General Forum Members Points: 186 Visits: 473 Hi friends,i need the output like Accno 205produ 206produ 208produ totalE-SW-2100 15000 8000 20000 42000after find the sum of account how to calculate the total value of each costcenter for that account Koen Verbeeck SSCoach Group: General Forum Members Points: 16516 Visits: 13210 Is it always 205, 206 and 208, or are there more columns possible? How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence raghuldrag SSC-Enthusiastic Group: General Forum Members Points: 186 Visits: 473 Hi Friends, The costcneter account wont be changed always same account and unique raghuldrag SSC-Enthusiastic Group: General Forum Members Points: 186 Visits: 473 Hi Friends , i just added my code with rollupselect fs_locn, fs_accno,fs_cost_center,amount=sum(case when fs_accno like 'E%' and fs_tran_type='Dr' then fs_amountwhen fs_accno like 'E%' and fs_tran_type='Cr' then fs_amount * -1end)from accutn_det where fs_trans_date between '01-may-2014' and '31-may-2014'groupby fs_locn,fs_accno,fs_cost_center with rollupits giving the sum total value with NULLhow to avoid "Null" replace on TOTAL In That??? Hardy21 Ten Centuries Group: General Forum Members Points: 1204 Visits: 1399 Give it a try:`; WITH AccountCTE AS (SELECT fs_accno, fs_cost_center, fs_amount from accutn_det)SELECT fs_accno,[205produ],[206produ],[208produ], COALESCE([205produ], 0)+ COALESCE([206produ], 0)+ COALESCE([208produ],0) AS Total FROM AccountCTEPIVOT (SUM(fs_amount) FOR fs_cost_center IN ([205produ],[206produ],[208produ])) p` Thanks