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?

score 2 · Accepted Answer · edited Jun 05 '13 at 07:13

2

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'

edited Jun 05 '13 at 07:13

fthiella

48,073
15
90
106

answered Jun 05 '13 at 07:07

PSR

39,804
41
111
151

score 0 · Answer 2 · answered Jun 05 '13 at 07:04

You need to pivot the data, unfortunately this is not dynamic in mysql, try this:

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

Also you can do this pivot split by date , you just need to have it in the field list and group by it :

SELECT
    date_time,
    SUM(IF(person_id = 101,total_amount,0)) as person_101,
    SUM(IF(person_id = 102,total_amount,0)) as person_102,
    SUM(IF(person_id = 103,total_amount,0)) as person_103
FROM
    my_table
GROUP BY
    date_time

Thanks this works.... But what if the number of person increases dynamically i.e. 104, 105 and so on... Can we increase the number of columns returned by the query dynamically... — Nikhilesh Manurkar, Jun 05 '13 at 07:30

generate multiple sums from the same query

2 Answers2