Samila Samila - 3 months ago 9
SQL Question

How to Substract using Posgres within same column

I need to subtract the raw data using same column.
The formula is
(provisioned port-shelf x 16 ) - (provisioned summary - provisioned RCMM).

enter image description here

Anyone have idea so that i can improve my query.

Answer

Assuming I'm understanding your question correctly, given your sample data, your expected result should be 16 for A. Assuming so, here's one option using conditional aggregation:

select ne,
       max(case when type = 'port-shelf' then 16 * provisioned else 0 end) -
       max(case when type = 'summary' then provisioned else 0 end) - 
       max(case when type = 'rcmm' then provisioned else 0 end) as free_slot,
       insert_date
from yourtable
group by ne, insert_date