Concatenate Subqueries with Unions (one table with two condition)

Question

I have 1 table with 2 different conditions. I want to present AR, annual, AR / annual * 365.

the code below can run

select AR, AR*365 
from (select sum(total_amount) AR
from invoices
where issue_date is not null and closed_at is null
and issue_date <= 'January 1,2020' and issue_date <DATEADD (DAY, 1, 'December 31,2020') 
UNION
select sum(total_amount)
from invoices
where closed_at <= 'January 1,2020' and closed_at <DATEADD (DAY, 1, 'December 31,2020')
)x group by AR;

the result :

   AR
1,895.23            15,903,040.94
691,758.95          5,804,609,943.1

and then i want to display AR, annual, AR/annual*365 but get error :

select AR, annual, AR/annual*365 from
(select sum(total_amount) AR
from invoices
where issue_date is not null and closed_at is null
and issue_date <= 'January 1,2020' and issue_date <DATEADD (DAY, 1, 'December 31,2020')
UNION
select sum(total_amount) annual
from invoices
where closed_at <= 'January 1,2020' and closed_at <DATEADD (DAY, 1, 'December 31,2020'))x group by         
AR;

When you want to display the annual value of "annual" the first line/first select cannot be read ...

but annual can be read when "annual" from the first line we delete.

I want the "annual" from the first line / first select to be read so that it can be calculated with other values.

Does anyone know how to fix it?

Answer 1

You can use conditional aggregation as follows:

Select AR, annual, AR/annual*365 from
(select sum(case when issue_date is not null and closed_at is null
                  and issue_date <= 'January 1,2020' 
                  and issue_date <DATEADD (DAY, 1, 'December 31,2020') 
                 then total_amount end) AR,
        sum(case when closed_at <= 'January 1,2020' 
                  and closed_at <DATEADD (DAY, 1, 'December 31,2020') 
                 then total_amount end) as annual
   from invoices) t

It is possible that annual is 0 and it is used in division so use the appropriate logic there.