By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (
id INT,
price DECIMAL(18,2),
type VARCHAR(5)
);
INSERT INTO test VALUES
(1, 19.99, 'PERM'),
(1, 30.13, 'TEMP'),
(2, 14.44, 'SOME'),
(2, 15.55, 'SOME2'),
(3, 2.00, 'TEMP'),
(4, 0.00, 'TEMP'),
(4, 10.00, 'OTHER');
-- solution using GROUP_CONCAT
SELECT id, IFNULL(gTemp, gOther) AS listPrice
FROM (
SELECT id,
GROUP_CONCAT(CASE WHEN type = 'TEMP' THEN price ELSE NULL END) gTemp,
GROUP_CONCAT(CASE WHEN type <> 'TEMP' THEN price ELSE NULL END) gOther
FROM test
GROUP BY id
)t;
| id | listPrice |
|---|---|
| 1 | 30.13 |
| 2 | 14.44,15.55 |
| 3 | 2.00 |
| 4 | 0.00 |
-- solution using SUM
SELECT id, IFNULL(sumTemp, sumOther) AS sumPrice
FROM (
SELECT id,
SUM(CASE WHEN type = 'TEMP' THEN price ELSE NULL END) sumTemp,
SUM(CASE WHEN type <> 'TEMP' THEN price ELSE NULL END) sumOther
FROM test
GROUP BY id
)t;
| id | sumPrice |
|---|---|
| 1 | 30.13 |
| 2 | 29.99 |
| 3 | 2.00 |
| 4 | 0.00 |
-- solution using MAX
SELECT id, IFNULL(maxTemp, maxOther) AS maxPrice
FROM (
SELECT id,
MAX(CASE WHEN type = 'TEMP' THEN price ELSE NULL END) maxTemp,
MAX(CASE WHEN type <> 'TEMP' THEN price ELSE NULL END) maxOther
FROM test
GROUP BY id
)t;
| id | maxPrice |
|---|---|
| 1 | 30.13 |
| 2 | 15.55 |
| 3 | 2.00 |
| 4 | 0.00 |
-- solution using MIN
SELECT id, IFNULL(minTemp, minOther) AS minPrice
FROM (
SELECT id,
MIN(CASE WHEN type = 'TEMP' THEN price ELSE NULL END) minTemp,
MIN(CASE WHEN type <> 'TEMP' THEN price ELSE NULL END) minOther
FROM test
GROUP BY id
)t;
| id | minPrice |
|---|---|
| 1 | 30.13 |
| 2 | 14.44 |
| 3 | 2.00 |
| 4 | 0.00 |