Find sum of a column based on a value of a column(X) and get all values of another column where X existed

Question

I have the following table:

id	code	amount	qty
1	1	25	36
2	2	30	6
3	5	100	1
4	1	25	100
5	1	20	1
6	4	10	136
7	1	10	20

I want to find the sum of all amounts where code = 1, and for all such occurrences also want comma separated values of all qty and comma separated value of all ids.

Eg: The output should look like:

code	amount	quantities	ids
1	80	36, 100,1, 20	1,4,5, 7

I know I can do something like

SELECT 
code
,SUM(amount) 
FROM 
table1 
where code = 1 
group by code;

for getting the sum corresponding to that code but don't know how to get all such quantities and ids.

DBFiddle

Answer 1

You can simply use GROUP_CONCAT to group all your data:

SELECT 
  t.`code`,
  SUM(amount) ,
  GROUP_CONCAT(t.`qty` SEPARATOR ',') AS qtys,
  GROUP_CONCAT(t.`id` SEPARATOR ',') AS ids
FROM
  yourTable t 
WHERE t.`code` = 1 
GROUP BY t.`code` ;

GROUP_CONCAT by default uses comma (,) as separator, so you can write same query as:

SELECT 
  t.`code`,
  SUM(amount) ,
  GROUP_CONCAT(t.`qty`) AS qtys,
  GROUP_CONCAT(t.`id`) AS ids
FROM
  yourTable t 
WHERE t.`code` = 1 
GROUP BY t.`code` ;

If you want some other separators, you can exclusively define that too.