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

Question

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 I want to calculate total no of vote on a particular object type and id.

  SELECT 
     COUNT (ALL  [PK_ID]) AS [TOTAL_VOTE],
     COALESCE (
         ( SELECT 
               [IS_THUMBS_UP]
           FROM
               [votetable]
           WHERE
               [ob_type] = 400 AND
               [ob_id] = 4 AND
               [FK_VOTED_BY] = Nishant ),-1) AS [MY_VOTING]  
  FROM  
     [votetable]
  WHERE
     [ob_type] = 400 AND
     [ob_id]  = 4 

Here it give result

total vote = 4 and my vote = 0

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

So how do I get the correct value.

Answer 1

I suggest (SQLServer syntax) :

select sum(case [VOTE] when 1 then 1 else -1 end) as total_votes,
       sum((case [VOTE] when 1 then 1 else -1 end) *  
           (case when [user_id] = @username then 1 else 0) as user_votes
from [votetable]
where [ob_type] = @obtype and [ob_id] = @obid

@username, @obtype and @obid are variables with the desired user's name, object type and object ID.