MySQL SUM in different ways

Question

I have two tables

user_raters:

| id(int) | to_id(int) | value(int) | created_at(datetime)
|1        | 2          | 1          | 2009-03-01 00:00:00

EDIT: I changed the user_rater_id. history_user_raters.user_rater_id is related to user_raters.id

history_user_raters:

| id(int) | user_rater_id(int) | value(int) | created_at(datetime)
| 1       | 1                  | 1          | 2009-03-02 00:00:00
| 2       | 1                  | 1          | 2009-03-02 00:00:00
| 3       | 1                  | -1         | 2009-03-02 00:00:00
| 4       | 1                  | 1          | 2009-03-03 00:00:00
| 5       | 1                  | -1         | 2009-03-03 00:00:00
| 6       | 1                  | -1         | 2009-03-03 00:00:00
| 7       | 1                  | -1         | 2009-03-03 00:00:00

I want to count the sum of the values from history_user_raters as it relates to the to_id from user_raters. The result from the query should be:

| year | month | day | total | down | up
| 2009 | 3     | 2   | 1     | 1    | 2
| 2009 | 3     | 3   | -2    | 3    | 1

I have a query that is close, but it is not counting the up and down correctly. The total is right. Can some one help me write the query or new query that calculates correct up and down?

My current query:

SELECT 
 YEAR(history.created_at) AS `year`,
 MONTH(history.created_at) AS `month`,
 DAY(history.created_at) AS `day`,
 SUM(history.value) as `total`,

 (SELECT 
  abs(SUM(historydown.value)) 
 FROM `user_raters` as raterdown 
  INNER JOIN `history_user_raters` AS historydown 
 WHERE raterdown.to_id = 2 
  AND historydown.value = -1 
  AND date(historydown.created_at) 
 GROUP BY history.created_at) as down,

 (SELECT SUM(historyup.value) 
 FROM `user_raters` as raterup 
  INNER JOIN `history_user_raters` AS historyup 
 WHERE raterup.to_id = 2 
  AND historyup.value = 1  
  AND date(history.created_at) 
 GROUP BY raterup.to_id) as up 

FROM `user_raters` 
 INNER JOIN history_user_raters AS history ON user_raters.id = history.user_rater_id
WHERE (user_raters.to_id = 2) 
GROUP BY DATE(history.created_at)

Answer 1

I might see it too simply (and sorry I can't test with data at the moment), but I'm guessing the following trick with two CASE statements would do just what is needed

SELECT 
  YEAR(history.created_at) AS year,
  MONTH(history.created_at) AS month,
  DAY(history.created_at) AS day,
  SUM(history.value) as total,
  SUM(CASE WHEN history.value < 0 THEN history.value ELSE 0 END) as down,
  SUM(CASE WHEN history.value > 0 THEN history.value ELSE 0 END) as up
FROM `user_raters` 
INNER JOIN `history_user_raters` AS history
  ON user_raters.id = history.user_rater_id
WHERE (user_raters.to_id = 1)  -- or some other condition...
GROUP BY DATE(history.created_at)

Answer 2

EDIT: @OMG Ponies deleted his answer. This response make no sense now, but I am not going to delete my answer, because I think it is silly.

@OMG ponies

Your query runs, but it returns no results. I had to adjust it a bit to add the to_id in the main queries where clause

SELECT 
 YEAR( t.created_at ) AS `year` , 
 MONTH( t.created_at ) AS `month` , 
 DAY( t.created_at ) AS `day` , 
 SUM( t.value ) AS `total` , 
 MAX( COALESCE( x.sum_down, 0 ) ) AS down, 
 MAX( COALESCE( y.sum_up, 0 ) ) AS up
FROM history_user_raters AS t
JOIN user_raters AS ur ON ur.to_id = t.user_rater_id
LEFT JOIN (

 SELECT hur.user_rater_id, 
  SUM( hur.value ) AS sum_down
 FROM history_user_raters AS hur
 WHERE hur.value = -1
 GROUP BY hur.user_rater_id
) AS x ON x.user_rater_id = t.user_rater_id
LEFT JOIN (

 SELECT hur.user_rater_id, 
  SUM( hur.value ) AS sum_up
 FROM history_user_raters AS hur
 WHERE hur.value =1
 GROUP BY hur.user_rater_id
) AS y ON y.user_rater_id = t.user_rater_id
WHERE ur.to_id =1
GROUP BY YEAR( t.created_at ) , MONTH( t.created_at ) , DAY( t.created_at )