search

Home  >  Q&A  >  body text

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

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

P粉439804514P粉439804514231 days ago399

reply all(2)I'll reply

  • P粉578343994

    P粉5783439942024-04-02 00:22:58

    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

    reply
    0
  • P粉323374878

    P粉3233748782024-04-02 00:12:44

    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.

    reply
    0
  • Cancelreply