sql - sum function not working properly -
my database in sql server 2005:

my query is:
select * tradefile convert(datetime,sauda_date) 'mar 1 2013%' , scrip_code='dlf' , inst_type 'fut%' this gives me result:

in buy=1 , sell=2.
if make sum of buy qty i.e. buy_sell=1 3000
and when make sum sell trade qty i.e. buy_sell=2 3000
but when fire query getting same result follows:
select convert(varchar(11),sauda_date) sauda_date, sum(case when buy_sell = 1 , scrip_code='dlf' , sauda_date between convert(datetime,'01/03/2013') , convert(datetime,'06/04/2013') trade_qty else 0 end) buyqty, sum(case when buy_sell = 2 , scrip_code='dlf' , sauda_date between convert(datetime,'01/03/2013') , convert(datetime,'06/04/2013') trade_qty else 0 end) sellqty , sum(case when buy_sell = 1 , scrip_code='dlf' trade_qty else 0 end) -sum(case when buy_sell = 2 , scrip_code='dlf' trade_qty else 0 end) carryforword tradefile scrip_code='dlf' group convert(varchar(11),sauda_date) it gives me result as:

i.e. buyqty=5000 , sellqty 4000 while have calculated 3000 , 3000 respectively.
how can different? sum() function working wrong??
is query wrong?
the conditions quite different plausible results too.
examples:
- in first query, have condition
party_code. missing in second query. - the same goes
inst_type - the condition
sauda_datedifferent. first query queries march, 1st, while second queries between march, 1st , april, 6th.
Comments
Post a Comment