Table TRANSACTION:

I need to get to this:

Can someone help me?

I tried this but sadness

SELECT
    user_id AS "USER 
    SUM(trans_value)
FROM 
    TRANSACTION
WHERE 
    trans_value = 1
GROUP BY
    user_id
ORDER BY 1;

I need to get to this

CodePudding user response：

Use conditional aggregation:

SELECT user_id,
       SUM(CASE trans_type_id WHEN 1 THEN trans_value END) AS sum_trans_type_1,
       SUM(CASE trans_type_id WHEN 2 THEN trans_value END) AS sum_trans_type_2
FROM   transaction
GROUP BY user_id

or PIVOT:

SELECT *
FROM   transaction
PIVOT (
  SUM(trans_value)
  FOR trans_type_id IN (
    1 AS sum_trans_type_1,
    2 AS sum_trans_type_2
  )
)

Which, for the sample data:

CREATE TABLE transaction (TRANS_VALUE, USER_ID, TRANS_TYPE_ID) AS
SELECT 10, 1, 2 FROM DUAL UNION ALL
SELECT  5, 2, 1 FROM DUAL UNION ALL
SELECT 15, 1, 1 FROM DUAL UNION ALL
SELECT 20, 2, 2 FROM DUAL UNION ALL
SELECT 10, 1, 2 FROM DUAL UNION ALL
SELECT  5, 1, 2 FROM DUAL UNION ALL
SELECT 15, 3, 1 FROM DUAL UNION ALL
SELECT 20, 3, 1 FROM DUAL;

Both output:

fiddle