sql - sum function not working properly -


my database in sql server 2005:

enter image description here

my query is:

select *  tradefile convert(datetime,sauda_date) 'mar  1 2013%'    , scrip_code='dlf' , inst_type 'fut%' 

this gives me result:

enter image description here

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:

enter image description here

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:

  1. in first query, have condition party_code. missing in second query.
  2. the same goes inst_type
  3. the condition sauda_date different. first query queries march, 1st, while second queries between march, 1st , april, 6th.

Comments

Popular posts from this blog

monitor web browser programmatically in Android? -

Shrink a YouTube video to responsive width -

wpf - PdfWriter.GetInstance throws System.NullReferenceException -