By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test ( id INT, -- will be used for ordering
cat INT, -- will be used for aggregation
col1 INT, -- will be used for to get SUM
col2 INT, -- will be used for to get value from 1st row
col3 INT -- will be used for to get value from last row
);
INSERT INTO test VALUES
(1,1,11,111,1111), (2,1,22,222,2222), (3,1,33,333,3333),
(4,2,4,4,4), (5,2,5,5,5);
SELECT * FROM test;
| id | cat | col1 | col2 | col3 |
|---|---|---|---|---|
| 1 | 1 | 11 | 111 | 1111 |
| 2 | 1 | 22 | 222 | 2222 |
| 3 | 1 | 33 | 333 | 3333 |
| 4 | 2 | 4 | 4 | 4 |
| 5 | 2 | 5 | 5 | 5 |
SELECT cat,
SUM(col1) col1_sum,
SUBSTRING_INDEX(GROUP_CONCAT(col2 ORDER BY id), ',', 1) col2_first,
SUBSTRING_INDEX(GROUP_CONCAT(col3 ORDER BY id), ',', -1) col3_last
FROM test
GROUP BY cat;
| cat | col1_sum | col2_first | col3_last |
|---|---|---|---|
| 1 | 66 | 111 | 3333 |
| 2 | 9 | 4 | 5 |