Outer join removing duplicated columns-CodePudding

I'm trying to joint multiple data obtained trough a query like this:

    select 
        TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
        COUNT(1) as DESIRED_VALUE
    from
        MY_TABLE
    where 
        FIELD = 'DESIRED_VALUE'
    group by 1;

That results in data such as:

YEAR     DESIRED_VALUE1
2022-09  52
2022-10  117
2022-11  95
2023-01  73

YEAR_MONTH  DESIRED_VALUE2
2022-11     35
2022-12     30
2023-01     29

I want to end up with a table such as:

YEAR_MONTH  DESIRED_VALUE1  DESIRED_VALUE2
2022-09     52              NULL
2022-10     117             NULL
2022-11     95              35
2022-12     53              30 
2023-01     73              29

I don't have previous knowledge of which dates will be returned by each query, if that makes sense, so I can't infer if it's a left join, for instance. So I'm doing a full

with result_1 as 
    (
        query1
    ),
result_2 as 
    (
        query2
    ) 
select * 
from 
    result_1 
full outer join result_2
on  
    result_1.YEAR_MONTH = result_2.YEAR_MONTH

Which gives me this:

YEAR_MONTH  DESIRED_VALUE2  YEAR_MONTH_2    DESIRED_VALUE1
2023-01     29              2023-01         73
NULL        NULL            2022-10         117
2022-11     35              2022-11         95
NULL        NULL            2022-09         52
2022-12     30              NULL            NULL

But I want do display a single YEAR_MONTH column, that shows all existent values:

YEAR_MONTH  DESIRED_VALUE2 DESIRED_VALUE1
2023-01     29             73
2022-10     NULL           117
2022-11     35             95
2022-09     NULL           52
2022-12     30             NULL

To resolve that, I use:

COALESCE(DESIRED_VALUE1.YEAR_MONTH, DESIRED_VALUE2.YEAR_MONTH) as YEAR_MONTH

However, if I add more data:

with result_1 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE1
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE1'
        group by 1
    ),
result_2 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE2
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE2'

        group by 1
    ), 
result3 as 
    (
        select 
            TO_VARCHAR(CREATE_TIME, 'yyyy-MM') as YEAR_MONTH,
            COUNT(1) as DESIRED_VALUE3
        from
            MY_TABLE
        where 
            STATUS = 'DESIRED_VALUE3'
            and CONDITION = 'CONDITION'
        group by 1
    )
select 
    COALESCE(DESIRED_VALUE1.YEAR_MONTH, DESIRED_VALUE2.YEAR_MONTH, DESIRED_VALUE3.YEAR_MONTH) as YEAR_MONTH,
    DESIRED_VALUE1,
    DESIRED_VALUE2,
    DESIRED_VALUE3
from 
    result_1 
full outer join 
    result_2
on  
    result_1.YEAR_MONTH = result_2.YEAR_MONTH
full outer join 
    result_3
on
    result_2.YEAR_MONTH = result_3.YEAR_MONTH
order by YEAR_MONTH desc;

I start getting repeated YEAR_MONTH

YEAR_MONTH  DESIRED_VALUE1  DESIRED_VALUE2  DESIRED_VALUE3
2023-01     73              29              83
2022-12     53              30              57
2022-11     95              35              71
2022-10     NULL            39              NULL        
2022-10     117             NULL            NULL    
2022-09     18              NULL            NULL    
2022-09     52              NULL            NULL

I'm not sure what's the best way to approach this problem.

CodePudding user response：

If correctly understand what you're trying to accomplish, wouldn't it be a whole lot simpler (and a lot more performant) to just say

select to_varchar(CREATE_TIME, 'yyyy-MM')                         as YEAR_MONTH,
       sum( case STATUS when 'DESIRED_VALUE1' then 1 else 0 end ) as DESIRED_VALUE1,
       sum( case STATUS when 'DESIRED_VALUE2' then 1 else 0 end ) as DESIRED_VALUE2,
       sum( case STATUS when 'DESIRED_VALUE3' then 1 else 0 end ) as DESIRED_VALUE3
from MY_TABLE
where STATUS in ('DESIRED_VALUE1, DESIRED_VALUE2, DESIRED_VALUE3 )
group by 1
order by 1

CodePudding user response：

try this

;with cte(date, value1, value2) as(
select date, value1, null from query1
union
select date, null, value2 from query2
)
select date
    , sum(value1)
    , sum(value2)
from cte
group by date