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

CodePudding user response：

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;

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;

View on DB Fiddle

CodePudding user response：

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.