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粉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 |
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;
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.