How to write SQL query for aggregate total value for a particular condition -

vote table

pk_id  ob_type  ob_id  vote   user_id  voted_on     1      100      1     1   nishant  02/08/10     2      100      1     1      devi  02/08/10     3      100      1     1  virendra  02/08/10     4      100      1     0       xyz  02/08/10     5      200      1     1     xcxcx  02/08/10     6      200      1     1       cxc  03/02/11     7      200      1     0      xccx  03/02/11     8      200      1     0      xccx  03/02/11     9      300      1     1    xccxxc  03/02/11    10      300      1     1      xccx  03/02/11    11      300      1     1        sd  02/08/10    12      300      1     0   xccxxcc  02/08/10    13      400      4     0    cxxcxc  02/08/10    14      400      4     0      xccx  03/02/11    15      400      4     0       cxc  03/02/11    16      400      1     1       cxc  03/02/11 

here want calculate total no of vote on particular object type , id.

  select       count (all  [pk_id]) [total_vote],      coalesce (          ( select                 [is_thumbs_up]                           [votetable]                           [ob_type] = 400 ,                [ob_id] = 4 ,                [fk_voted_by] = nishant ),-1) [my_voting]            [votetable]        [ob_type] = 400 ,      [ob_id]  = 4  

here give result

total vote = 4 , vote = 0

but here total voting -2 (bcz 3 false(0) , 1 true)

so how correct value.

i suggest (sqlserver syntax) :

select sum(case [vote] when 1 1 else -1 end) total_votes,        sum((case [vote] when 1 1 else -1 end) *              (case when [user_id] = @username 1 else 0) user_votes [votetable] [ob_type] = @obtype , [ob_id] = @obid 

@username, @obtype , @obid variables desired user's name, object type , object id.