Optimally querying a database of ratings?

Question

I have a table of ratings that stores a user ID, object ID, and a score (+1 or -1). Now, when I want to display a list of objects with their total scores, the number of +1 votes, and the number of -1 votes.

How can I do this efficiently without having to do SELECT COUNT(*) FROM rating WHERE (score = +/-1 AND object_id = ..)? That's two queries per object displayed, which is unacceptable. Is the database design reasonable?

Answer 1

While it doesn't address your question of reasonable design, here's a query that gets you both counts at once:

select
    sum(case when score = 1 then 1 else 0 end) 'positive'
,   sum(case when score = -1 then 1 else 0 end) 'negative'
,   objectId
from
    ratings
where
    objectId = @objectId ...
group by
    objectId

Answer 2

select object_id, 
       sum(case when score = 1 then 1 else 0) upvotes, 
       sum(case when score = -1 then -1 else 0) downvotes,
       sum(score)
from ratings
group by object_id

Perhaps something like that.

Answer 3

This should do it:

SELECT
 UserID, ObjectID,
 SUM(CASE WHEN score=1 Then 1 Else 0 End) as UpVotes,
 SUM(CASE WHEN score=-1 Then 1 Else 0 End) as DownVotes,
FROM YourTable
GROUP BY UserID, ObjectID