mysql query: going crazy

Question

Hi all!

I'm having some trouble running a query on a table like this:

+-----+---------------------+-------+------+
| id  | paid_date           | amount| type |
+-----+---------------------+-------+------+
| 204 | 2010-10-22 05:12:54 |  1000 |    0 |
| 205 | 2010-10-22 05:13:12 |  1000 |    1 |
| 206 | 2010-10-21 05:13:44 |  1000 |    0 |
| 208 | 2010-10-22 05:57:33 |  1000 |    1 |
+-----+---------------------+-------+------+

The type column determines whether the money comes in or out, so I'd like to run a query that could gave me this result

+---------------------+-------+------+
| DATE(paid_date)     | in    | out  |
+---------------------+-------+------+
| 2010-10-21          |  1000 |    0 |
| 2010-10-22          |  1000 | 2000 |
+---------------------+-------+------+

I don't know what am I doing wrong, I know it's not so difficult, but can't make it happen :(

Answer 1

   SELECT DATE(paid_date), 
   SUM(CASE type when 0 then amount else 0 end) in,
   SUM(CASE type when 1 then amount else 0 end) out
   FROM Table
   GROUP BY DATE(paid_date)