Link to home
Start Free TrialLog in
Avatar of Henk_du_Toit
Henk_du_Toit

asked on

subtotal of dept by ledger account

Hi I have a query to get the data I want from a table and getting the subtotal of each ledger account. I am however having trouble getting the subtotal for each department by ledger account. Can you please show me how to add to the attached query the subtotal for each dept?

Thank you very much  
select leac,dim1,dim2,amount1,amount2,amount3,
sum(amount1) over(partition by leac) as subleac1,
sum(amount2) over(partition by leac) as subleac2,
sum(amount3) over(partition by leac) as subleac3
from
(select
ac.t$leac as leac,
ac.t$d1dc as dim1,
ac.t$d2dc as dim2,
SUM(case when ac.t$perd = '1' then (ac.t$fdah) else 0 end) -
SUM(case when ac.t$perd = '1' then (ac.t$fcah) else 0 end) As amount1,
SUM(case when ac.t$perd = '2' then (ac.t$fdah) else 0 end) -
SUM(case when ac.t$perd = '2' then (ac.t$fcah) else 0 end) As amount2,
SUM(case when ac.t$perd = '3' then (ac.t$fdah) else 0 end) -
SUM(case when ac.t$perd = '3' then (ac.t$fcah) else 0 end) As amount3
from ttffst305099 ac
where ac.t$year = '2011'
and ac.t$fcom = '99'
and ac.t$ptyp = '1'
and ac.t$budg = 'ACT'
and ac.t$hcur = 'NZD'
and ac.t$leac in ('50000','60000')
group by ac.t$ldes, ac.t$leac, ac.t$d1dc, ac.t$d2dc)
order by leac,dim1,dim2


leac	dept	emp	apr 		may 		jun 		subt_apr_leac	subt_may_leac	subt_jun_leac
50000	70	1	11176.01	11676.01	11476.01	40670.09	44670.09	43070.09
50000	70	2	4413.27		4913.27		4713.27		40670.09	44670.09	43070.09
50000	70	3	6511.06		7011.06		6811.06		40670.09	44670.09	43070.09
50000	50	4	631.73		1131.73		931.73		40670.09	44670.09	43070.09
50000	50	5	4482.71		4982.71		4782.71		40670.09	44670.09	43070.09
50000	50	6	5079.44		5579.44		5379.44		40670.09	44670.09	43070.09
50000	95	7	4378.98		4878.98		4678.98		40670.09	44670.09	43070.09
50000	95	8	3996.89		4496.89		4296.89		40670.09	44670.09	43070.09
60000	70	1	1142.31		1642.31		1442.31		3207.41		7207.41		5607.41
60000	70	2	476.8		976.8		776.8		3207.41		7207.41		5607.41
60000	70	3	414.21		914.21		714.21		3207.41		7207.41		5607.41
60000	50	4	360.94		860.94		660.94		3207.41		7207.41		5607.41
60000	50	5	313.61		813.61		613.61		3207.41		7207.41		5607.41
60000	50	6	215.51		715.51		515.51		3207.41		7207.41		5607.41
60000	95	7	284.03		784.03		584.03		3207.41		7207.41		5607.41
60000	95	8	0		500 		300 		3207.41 	7207.41 	5607.41


The end result I want to get is this:
leac	dept	emp	apr 		may 		jun 		subt_apr_leac	subt_may_leac	subt_jun_leac	subt_apr_dept	subt_may_dept	subt_jun_dept
50000	70	1	11176.01	11676.01	11476.01	40670.09	44670.09	43070.09	22100.34	23600.34	23000.34
50000	70	2	4413.27		4913.27		4713.27		40670.09	44670.09	43070.09	22100.34	23600.34	23000.34
50000	70	3	6511.06		7011.06		6811.06		40670.09	44670.09	43070.09	22100.34	23600.34	23000.34
50000	50	4	631.73		1131.73		931.73		40670.09	44670.09	43070.09	10193.88	11693.88	11093.88
50000	50	5	4482.71		4982.71		4782.71		40670.09	44670.09	43070.09	10193.88	11693.88	11093.88
50000	50	6	5079.44		5579.44		5379.44		40670.09	44670.09	43070.09	10193.88	11693.88	11093.88
50000	95	7	4378.98		4878.98		4678.98		40670.09	44670.09	43070.09	8375.87 	9375.87 	8975.87
50000	95	8	3996.89		4496.89		4296.89		40670.09	44670.09	43070.09	8375.87 	9375.87 	8975.87
60000	70	1	1142.31		1642.31		1442.31		3207.41		7207.41		5607.41 	2033.32 	3533.32 	2933.32
60000	70	2	476.8		976.8		776.8		3207.41		7207.41		5607.41 	2033.32 	3533.32 	2933.32
60000	70	3	414.21		914.21		714.21		3207.41		7207.41		5607.41 	2033.32 	3533.32 	2933.32
60000	50	4	360.94		860.94		660.94		3207.41		7207.41		5607.41 	890.06  	2390.06 	1790.06
60000	50	5	313.61		813.61		613.61		3207.41		7207.41		5607.41 	890.06  	2390.06 	1790.06
60000	50	6	215.51		715.51		515.51		3207.41		7207.41		5607.41 	890.06  	2390.06 	1790.06
60000	95	7	284.03		784.03		584.03		3207.41		7207.41		5607.41 	284.03  	1784.03 	884.03
60000	95	8	0		500 		300 		3207.41 	7207.41 	5607.41 	284.03  	1784.03 	884.03

Open in new window

Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

in the current output i see some columns with apr , may , june but your query does not have those columns ? so how do you get them ?

Would be good if you can give your entire query so that one can see whether anything is incorrect there.
Avatar of Henk_du_Toit
Henk_du_Toit

ASKER

apr may june should read amount1, amount2 and amount3. Sorry I did the example on notepad and forgot to change the column descriptions. This is the entire query.
where is the query ?
on the first post
oh..you mean to say that is the query itself but you forgot to change teh column names.. Got it.
Can you just take 1 record from the current output and expected output for just one column which is incorrect and tell me what is incorrect there ?

Current output is correct like I want it but want to add to it and do not know how to do it. The expected output is what I want.

The function i think that is needed would be similar to excel's sumifs function.

ie:
Just quickly without running query, this works correctly with my query
leac          dept      emp       amnt1      subtleac
50000      Admin    Joe         50          330
50000      admin     chris      30          330
50000      Sales     mike       100        330
50000      Sales     sally       150        330

but what I want to add is
leac          dept      emp       amnt1      subtleac   subtdept
50000      Admin    Joe         50          330           80
50000      admin     chris      30          330           80
50000      Sales     mike       100        330           250
50000      Sales     sally       150        330           250

Does that make any sense?
ASKER CERTIFIED SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great, I thought I did try that and didnt work... It works now though.

Thank you very much for your help