Generate two SUM value columns for two different dates separately in one query-CodePudding

I am trying to combine two queries

  select s.id , sum(h.val)  as [VAL]
    from identity s inner join price_net h on s.date = h.date and s.num_id = h.num_id and s.rec_type = h.rec_type
    where s.date = '10/25/21'
    and s.rec_type = 'D'
    and s.tier = 'P'
    group by s.

select s.id , sum(h.val)  as [VAL]
        from identity s inner join price_net h on s.date = h.date and s.num_id = h.num_id and s.rec_type = h.rec_type
        where s.date = '10/26/21'
        and s.rec_type = 'D'
        and s.tier = 'P'
        group by s.

and I want it so there is one table with one column for s.id and the next columns are VAL columns for each respective date. Where a value is not available for a give s.id on a specific date it should show NA.

CodePudding user response：

I supppose you are looking for conditional aggregation (CASE WHEN inside the aggregation function).

select
  i.id,
  sum(case when i.date = date '2021-10-25' then pn.val end) as val_20211025,
  sum(case when i.date = date '2021-10-26' then pn.val end) as val_20211026
from identity i
join price_net pn on pn.date = i.date 
                 and pn.num_id = i.num_id
                 and pn.rec_type = i.rec_type
where i.tier = 'P'
and i.rec_type = 'D'
and i.date in (date '2021-10-25', date '2021-10-26')
group by s.id
order by s.id;

CodePudding user response：

you can use case when for both criteria

select s.id , sum(CASE WHEN s.date = '10/25/21' THEN h.val eLSE  O END)  as [VAL]
, sum(CASE WHEN s.date = '10/26/21' THEN h.val eLSE  O END)  as [VAL2]
        from identity s inner join price_net h on s.date = h.date and s.num_id = h.num_id and s.rec_type = h.rec_type
        where 
        as.rec_type = 'D'
        and s.tier = 'P'
        group by s.ìd