-1

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?

ryan
  • 53
  • 6
  • Please provide sample data, desired results, an appropriate database tag, and a clear explanation of what you want to do. – Gordon Linoff Jan 07 '21 at 03:11
  • I think you want to `cross join` these two results rather than `union`. That way you can access both values at once. Bu aside from that, other issues you have are: `union` will distinct-ify and remove identical results, you want `union all`. The date filters don't make sense, did you mean `>= and <`. No need to `group by AR` in your outer query as they will be just two different values anyway. Specify date literals like this `'20201231'` – Charlieface Jan 07 '21 at 03:21
  • Thankyou @GordonLinoff – ryan Jan 14 '21 at 02:29
  • Thankyou @Charlieface – ryan Jan 14 '21 at 02:29

1 Answers1

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.

Popeye
  • 35,427
  • 4
  • 10
  • 31