You must Sign In to post a response.
  • Category: SQL Server

    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?
  • #747337
    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

  • #747339
    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

  • #747345
    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???

  • #747353
    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 cost
    from tablename
    group 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/


  • Sign In to post your comments