Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to calculate total sum of these table Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, July 11, 2014 12:13 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, July 27, 2016 4:25 AM 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?
Post #1591438
 Posted Friday, July 11, 2014 12:36 AM
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 2:09 AM Points: 15,501, Visits: 13,163
 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
Post #1591447
 Posted Friday, July 11, 2014 12:45 AM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, August 23, 2016 12:03 AM Points: 1,134, Visits: 1,399
 Add Cost center (fs_cost_center) in Group by clause. Thanks
Post #1591459
 Posted Friday, July 11, 2014 3:07 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, July 27, 2016 4:25 AM Points: 186, Visits: 473
 Hi Friends i need out put likefor exmaple :account no 205prod 206prod totalE-SW-100 10000 3000 13000how make code?
Post #1591493
 Posted Friday, July 11, 2014 3:17 AM
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 2:09 AM Points: 15,501, Visits: 13,163
 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
Post #1591500
 Posted Friday, July 11, 2014 3:35 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, July 27, 2016 4:25 AM 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
Post #1591503
 Posted Friday, July 11, 2014 3:55 AM
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 2:09 AM Points: 15,501, Visits: 13,163
 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
Post #1591531
 Posted Friday, July 11, 2014 4:22 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, July 27, 2016 4:25 AM Points: 186, Visits: 473
 Hi Friends, The costcneter account wont be changed always same account and unique
Post #1591537
 Posted Friday, July 11, 2014 4:52 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, July 27, 2016 4:25 AM 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???
Post #1591551
 Posted Friday, July 11, 2014 5:04 AM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, August 23, 2016 12:03 AM Points: 1,134, Visits: 1,399
 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
Post #1591558

 Permissions