How to calculate total sum of these table

Question

How to calculate total sum of these table

raghuldrag

Hall of Fame

Points: 3160
More actions
July 11, 2014 at 12:13 am

#397043

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?

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

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

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

Add Cost center (fs_cost_center) in Group by clause.

Thanks

raghuldrag Hall of Fame Points: 3160 More actions · Answer 3

Hi Friends

i need out put like

for exmaple :

account no 205prod 206prod total

E-SW-100 10000 3000 13000

how make code?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

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

raghuldrag Hall of Fame Points: 3160 More actions · Answer 5

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

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

raghuldrag Hall of Fame Points: 3160 More actions · Answer 7

Hi Friends,

The costcneter account wont be changed always same account and unique

raghuldrag Hall of Fame Points: 3160 More actions · Answer 8

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???

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 9

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

raghuldrag Hall of Fame Points: 3160 More actions · Answer 10

raghuldrag

Hall of Fame

Points: 3160

July 11, 2014 at 5:10 am

#1728684

Hi Friend

In MSSql2000 wont allow Pivot

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 11

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

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 12

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

David Burrows SSC Guru Points: 65143 More actions · Answer 13

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.