MySQL Aggregration?

Question

I have the following two tables

Table: items
ID  |  TITLE
249 | One
250 | Two
251 | Three

And I have voting for these:

Table: votes
VID | IID | userid | votes
01  | 249 |    6   | 5
02  | 249 |    7   | -5
03  | 249 |    8   | 5
04  | 249 |    9   | 5
05  | 250 |    6   | -5
06  | 250 |    7   | -5
07  | 250 |    8   | 5

-5 means a DOWNVOTE, and +5 means an upvote. Assuming I am logged in as user 6, what SQL query will give me:

Table: result
ID  |  TITLE | TOTALVOTES | UPVOTES | DOWNVOTES | CURRENTUSERVOTED
249 | One    | 4          | 3       | 1         | 1
250 | Two    | 3          | 1       | 2         | 1
251 | Three  | 0          | 0       | 0         | 0

Answer 1

Use CASE expressions within your aggregate functions:

SELECT    a.ID,
          a.TITLE,
          COUNT(b.IID) AS TOTALVOTES,
          COUNT(CASE WHEN b.votes =  5 THEN 1 END) AS UPVOTES,
          COUNT(CASE WHEN b.votes = -5 THEN 1 END) AS DOWNVOTES,
          COUNT(CASE WHEN b.userid = 6 THEN 1 END) AS CURRENTUSERVOTED
FROM      items a
LEFT JOIN votes b ON a.ID = b.IID
GROUP BY  a.ID,
          a.TITLE

Answer 2

this should work

select I.Id, I.Title, count(*) as TotalVotes,
sum(case when votes > 0 then 1 else 0 end) as UPVOTES, 
sum(case when votes < 0 then 1 else 0 end) as DOWNVOTES,
case when sum(Case when userid = @userloged then 1 else 0 end) = 1 then 1 else 0 end as CURRENTUSERVOTED
from items I
LEFT JOIN Votes V
on I.Id = V.IID
group by I.ID, I.Title

probably are better ways to get CURRENTUSERVOTED