generate multiple sums from the same query

Question

I have a table like this:

id   person_id  total amount    date_time
1    101        2000            2001-12-10
2    102        1000            2001-12-10
3    102        3000            2001-12-10
4    102        2000            2001-12-10
5    103        1000            2001-12-11
6    101        1000            2001-12-11
7    102        3000            2001-12-11
8    102        4000            2001-12-11
9    102        4000            2001-12-11

I want the output to be like the one below for the date 2001-12-11

person_101   person_102   person_103
1000         11000         1000

I've tried using the SUM() function but am stuck with the WHERE and JOIN clauses. How do I solve this?

Answer 1

SELECT
  SUM(CASE WHEN person_id = 101 THEN total_amount ELSE 0 END) as person_101,
  SUM(CASE WHEN person_id = 102 THEN total_amount ELSE 0 END) as person_102,
  SUM(CASE WHEN person_id = 103 THEN total_amount ELSE 0 END) as person_103
FROM
  my_table
WHERE
  date_time ='2001-12-11'