Find the sum of columns based on the values ​​of column (X) and get all values ​​of another column where X exists

Question

I have the following table:

id	Code	Amount	quantity
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 for which code = 1 and also need comma separated values ​​for all quantities and comma separated values ​​for all ids for all such events.

For example: The output should look like this:

Code	Amount	quantity	id
1	80	36, 100,1, 20	1,4,5,7

I know I can do something similar

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

is used to get the sum corresponding to this code, but don't know how to get all such quantities and IDs.

DBFiddle

Answer 1

In MySQL you can use GROUP_CONCAT

Query#1

select
        code,
        sum(amount)  as total_amount,
        GROUP_CONCAT(id) as ids,
        GROUP_CONCAT(qty) qts

    from yourTable
    where code = 1
    GROUP BY code;

Code	total_amount	id	qts
1	80	1,4,5,7	36,100,1,20

---

View on DB Fiddle

In Postgres you can use string_agg

Query#1

select
        code,
        sum(amount)  as total_amount,
        string_agg(id::text,',') as ids,
        string_agg(qty::text , ',') qts

    from yourTable
    where code = 1
    GROUP BY code;

Code	total_amount	id	qts
1	80	1,4,5,7	36,100,1,20

---

View on DB Fiddle

Answer 2

You can simply use GROUP_CONCAT to group all 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 delimiter so you can write the same query:

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 delimiter, you can also define it specifically.