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_date
different. first query queries march, 1st, while second queries between march, 1st , april, 6th.
Comments
Post a Comment