Samila Samila - 1 year ago 102
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download