By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH emp AS (
SELECT 7369 AS id, 'SMITH' AS name, 1 AS order_id, 5 AS VALUE UNION ALL
SELECT 7499, 'ALLEN', 2, 10 UNION ALL
SELECT 7521, 'JONES', 3, 15 UNION ALL
SELECT 7566, 'JONES', 4, 5 UNION ALL
SELECT 7568, 'JONES', 5, 10
),
cte AS (
SELECT *, MIN(order_id) OVER (PARTITION BY name) min_order_id
FROM emp
)
SELECT
name,
MIN(order_id) AS f_order,
MAX(order_id) AS l_order,
SUM(VALUE) AS total_order_value,
SUM(CASE WHEN order_id = min_order_id THEN VALUE ELSE 0 END) AS f_order_value
FROM cte
GROUP BY
name;
| name | f_order | l_order | total_order_value | f_order_value |
|---|---|---|---|---|
| ALLEN | 2 | 2 | 10 | 10 |
| JONES | 3 | 5 | 30 | 15 |
| SMITH | 1 | 1 | 5 | 5 |