Why can't I correctly group data in SQL?

user_15473314 0
Viewed 40

I am trying to pull data and format with these headers in this order.

I'm pulling the data from Snowflake using SQL, and in the table I'm pulling the data from, the PD_amt and CN_amt are listed as separate transactions. Currently, when I pull the data, it still shows up as two separate lines with null values rather than being correctly grouped into a single row under a single user id; you can see it here in the output (I highlighted a few rows to show the issue).

select atr.user_id
     , date_trunc('day', atr.trans_date) trans_day
     , atr.year_month
     , case when atr.trans_type = 'PD' then atr.trans_id end as PD_transaction_id
     , case when atr.trans_type = 'PD' then sum(atr.amount) end as PD_amt
     , case when atr.trans_type = 'CN' then atr.trans_id end as CN_transaction_id
     , case when atr.trans_type = 'CN' then sum(atr.amount) end as CN_amt
  from wisen_data.sm_account_trans atr
 where ((trans_type = 'CN' and trans_sub_type = 'HSNMLP')
         or trans_type in ('PP','PD'))
   and atr.status not in  ('VOIDED','FAILED')
   and atr.trans_date >= date_trunc('month', current_date-3*365)
group by atr.user_id, trans_day, atr.year_month, atr.trans_type, atr.trans_id
order by trans_day

I'm not super proficient with SQL so I'm hoping to get some quick help to get this to work. Thank you!

snowflake-cloud-data-platform sql
1 Answers

Use conditional aggregation. The case expression is the argument to the sum():

select atr.user_id,
       date_trunc('day', atr.trans_date) as trans_day
       atr.year_month
       sum(case when atr.trans_type = 'PD' then atr.amount end) as PD_amt,
       sum(case when atr.trans_type = 'CN' then atr.amount end) as CN_amt
  from wisen_data.sm_account_trans atr
 where ((trans_type = 'CN' and trans_sub_type = 'HSNMLP')
         or trans_type in ('PP','PD')
       ) and
       atr.status not in  ('VOIDED','FAILED')
       atr.trans_date >= date_trunc('month', current_date-3*365)
group by atr.user_id, trans_day, atr.year_month
order by trans_day;

I removed PD_transaction_id and CN_transaction_id because I'm not sure what these are supposed to be.

user_11440350
Related