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?

  • Add the cost center in the GROUP BY?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Add Cost center (fs_cost_center) in Group by clause.

    Thanks

  • Hi Friends

    i need out put like

    for exmaple :

    account no 205prod 206prod total

    E-SW-100 10000 3000 13000

    how make code?

  • raghuldrag (7/11/2014)


    Hi Friends

    i need out put like

    account no 205prod 206prod total

    E-SW-100 10000 3000 13000

    how make code?

    How is the 3000 calculated?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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

  • Is it always 205, 206 and 208, or are there more columns possible?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hi Friends,

    The costcneter account wont be changed always same account and unique

  • 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

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

  • 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 AccountCTE

    PIVOT (SUM(fs_amount) FOR fs_cost_center IN ([205produ],[206produ],[208produ])) p

    Thanks

  • Hi Friend

    In MSSql2000 wont allow Pivot

  • raghuldrag (7/11/2014)


    Hi Friend

    In MSSql2000 wont allow Pivot

    Don't you think this was worth mentioning in the first place?

    You posted your topic in a SQL Server 2008 forum.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • raghuldrag (7/11/2014)


    Hi Friend

    In MSSql2000 wont allow Pivot

    You have not mentioned SQL2000 anywhere in the thread. Also you have posted this question in 2008 forum.

    Don't you think you need to share this with your question?

    Thanks

  • SELECTfs_accno,

    SUM(CASE WHEN fs_cost_center = '205produ' THEN amount ELSE 0.00 END) AS [205produ],

    SUM(CASE WHEN fs_cost_center = '206produ' THEN amount ELSE 0.00 END) AS [206produ],

    SUM(CASE WHEN fs_cost_center = '208produ' THEN amount ELSE 0.00 END) AS [208produ]

    FROM(

    SELECTfs_accno,fs_cost_center,

    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) AS [amount]

    FROM#accutn_det

    WHEREfs_trans_date BETWEEN '01-may-2014' AND '31-may-2014'

    GROUPBY fs_accno,fs_cost_center

    ) a

    GROUPBY fs_accno

    You will have to convert this into a dynamic pivot for a variable number of cost centres.

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply