You must Sign In to post a response.
• # How to calculate total sum of these table

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 amount

so i made the query to find the amount for may

select
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_accno

now i need the sum values of all costcenter for the particular account

how to do that?
• select

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'
and fs_accno='E-Sw-2100'
group by fs_accno

• Hi friends,

i need the output like

Accno 205produ 206produ 208produ total

E-SW-2100 15000 8000 20000 42000

after find the sum of account how to calculate the total value of each costcenter for that account

• Hi Friends ,

i just added my code with rollup

select
fs_locn,
fs_accno,
fs_cost_center,
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_accno,fs_cost_center with rollup

its giving the sum total value with NULL like

how to avoid "Null" replace on TOTAL In That???

• Hi,

If you want to calculate total against particular field then using SUM function and GroupBy clause you can achieve this.

`select SUM(fs_cost_center) as costfrom tablenamegroup by fs_accno `

Try something like above to achieve your goal...

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/