I have this query:

select *
from(
select record_id, hood_id, city_id, TotalDays
from (
    select *, 
        row_number() over(partition by record_id order by end_date desc) rn,
        sum(date_diff('day', start_date, end_date)) over(partition by record_id) as TotalDays
    from ads 
    where del_col = false
)
where rn = 1
union  
select record_id, hood_id, city_id, TotalDays
from (
    select *, 
        row_number() over(partition by record_id order by end_of_life desc) rn,
        sum(date_diff('day', start_date, end_of_life)) over(partition by record_id) as TotalDays
    from ads 
    where del_col = true
)
where rn = 1)
where record_id = 146 and TotalDays <= 60 and TotalDays >= 0
group by record_id, hood_id, city_id, TotalDays

and the output its:

I want this output:

someone have a good idea to solution this problem?

CodePudding user response：

You just have to add TotalDays using the SUM function.

select record_id, hood_id, city_id, SUM(TotalDays) as TotalDays
from(
select record_id, hood_id, city_id, TotalDays
from (
    select *, 
        row_number() over(partition by record_id order by end_date desc) rn,
        sum(date_diff('day', start_date, end_date)) over(partition by record_id) as TotalDays
    from ads 
    where del_col = false
)
where rn = 1
union  
select record_id, hood_id, city_id, TotalDays
from (
    select *, 
        row_number() over(partition by record_id order by end_of_life desc) rn,
        sum(date_diff('day', start_date, end_of_life)) over(partition by record_id) as TotalDays
    from ads 
    where del_col = true
)
where rn = 1)
where record_id = 146 and TotalDays <= 60 and TotalDays >= 0
group by record_id, hood_id, city_id