aggregate function on sum and max on 2 table in sql -
i have 2 table invm
, invt
invm
has
i_b_no , i_b_dt , freight , tot_amt 1001 , 04/01/2015, 805 , 5000 1002 , 04/01/2015, 205 , 300
invt
has
i_b_no , item_code, item_desc , stax_rate , amount 1001 , xyz , adlasjdlja, 12.5, 1000 1001 , abc , dfjsdffsdf, 12.5, 1000 1001 , lkm , sfkgsfksdn, 10.5, 1000 1001 , rmn , yeryoueoqe, 5.00, 1000 1001 , , qwelskjfsdf, 12.5, 1000 1002 , dad , sdfkdsffsdf , 10.0, 100 1002 , dekw , kdasdahdafa , 3.2 , 100 1002 , dasdna , kdfnasldakdf, 6.8 , 100
now want sum ( (freight) * max(staxrate) /100 )
which (805* 12.5)/100 = 1006.25
(205 * 10)/100 = 20.5
ans should 1026.75
i used
select sum (( sum (invm.freight ) * max (invt.stax_rate) /100 )) invm inner join invt on invm.i_b_no = invt.i_b_no invm.i_b_dt between '04/01/2015 00:00:00' , '04/01/2015 00:00:00' group invm.freight
gives me error
msg 130, level 15, state 1, line 1 cannot perform aggregate function on expression containing aggregate or subquery
.
i stucked in please
do join
in derived table group by
, max
:
select sum (freight * stax_rate / 100) (select invm.freight freight , max(invt.stax_rate) stax_rate invm inner join invt on invm.i_b_no = invt.i_b_no invm.i_b_dt between '04/01/2015 00:00:00' , '04/01/2015 00:00:00' group invm.freight)
Comments
Post a Comment