How do I group together and return multiple values associated with the same date?

user_9158518 0
Viewed 210

So basically as an example, here's a table of foods and the date they were consumed on by different people

consumption

user_id food date
1 apple 12-DEC-09
1 banana 12-DEC-09
1 strawberry 13-DEC-09
2 apple 14-DEC-09
2 apple 15-DEC-09
2 orange 15-DEC-09

I want to select all foods that each user_id ate on each unique date, so the output looks something like:

user_id food date
1 apple, banana 12-DEC-09
1 strawberry 13-DEC-09
2 apple 14-DEC-09
2 apple, orange 15-DEC-09

I've tried something like this:

SELECT food
FROM consumption
WHERE food IN (
    SELECT food
    FROM consumption
    GROUP BY food, `date`
)
GROUP BY user_id;

But I'm stumped. Any help would be appreciated!

select mysql sql
1 Answers

The group in your case is rows group where both user_id and date is the same. Hence you must group by these two columns.

You need all food names concatenated into single value within a group. Hence you must use according aggregate function.

SELECT user_id, GROUP_CONCAT(food) food_list, `date`
FROM consumption
GROUP BY user_id, `date`;
user_101387340
Related