CODEWITHSUNDEEP
sqlteradata
Abasesha
Abasesha

Reputation: 41

Count based on merge column

I have a table with the below structure.

enter image description here

up_cnt_1 corresponds to dt_1 and up_cnt_2 corresponds to dt_2 column . I want to aggregate up_cnt_1 grouped by dt_1 and up_cnt_2 grouped by dt_2 but the final table should have a single column for these dates , although counts will be separately mentioned. The merged date column basically has the unique values of dt_1 and dt_2 and for any date if the value of corresponding count is missing it is shown as 0.

The expected output is as below:

enter image description here

I am using Teradata 16 as RDBMS

Upvotes: 0

Views: 234

Answers (1)

GMB
GMB

Reputation: 222482

One option uses union all and aggregation:

select dt_merge, sum(up_cnt_1) up_cnt_1, sum(up_cnt_2) up_cnt_2
from (
    select dt_1 dt_merge, up_cnt_1, 0 up_cnt_2 from mytable 
    union all 
    select dt_2, 0, up_cnt_2 from mytable 
) t
group by dt_merge

Upvotes: 1

Related Questions