Aggregate function will not aggregate - while using sum function to aggregate dataset

Question

I am new to snowflake and I am trying to write an SQL query that would sum up 2 rows based on the ref column. The sum function seems to not work as I think im missing something.

sample data (transaction)

| id   | withdraw| pending|   ref   |  Status  |   date   |
|:---- |:-------:| --- --:| -------:|---------:|---------:|
| 100  | 500     | -500   |1234:234 |Confirmed |2022-05-04 |
| 100  | -500    |  500   |1234:234 |Pending   |2022-05-03 |

what I want to achieve

| id   | withdraw| pending|   ref   |  Status  |   date   |
|:---- |:-------:| --- --:| -------:|---------:|---------:|
| 100  | 0       |   0    |1234:234 |Confirmed |2022-05-04 |

here is what I did so far

select id,
       sum(withdraw),
       sum(pending),
       ref,
       status,
       date,
from transaction
group by id,ref

Answer 1

If what you want is the sum of withdraw and pending and the last status and date per id and ref then use window functions:

SELECT DISTINCT 
       id,
       SUM(withdraw) OVER (PARTITION BY id, ref) withdraw,
       SUM(pending) OVER (PARTITION BY id, ref) pending,
       ref,
       FIRST_VALUE(status) OVER (PARTITION BY id, ref ORDER BY date DESC) status,
       MAX(date) OVER (PARTITION BY id, ref) date
FROM transaction;