CODEWITHSUNDEEP
sqlpostgresql
Luis Henrique
Luis Henrique

Reputation: 771

How to calculate metrics between two tables

How to calculate metrics between two tables? In addition, I noticed that when using FROM tbl1, tbl2, there are noises, the WHERE filters did not work, a total count(*) was returned

Query:

select
    count(*) filter(WHERE tb_get_gap.system in ('LINUX','UNIX')) as gaps,
    SUM(CAST(srvs AS INT)) filter(WHERE tb_getcountsrvs.type = 'LZ') as total,
    100 - (( gaps / total ) * 100)
FROM tb_get_gap, tb_getcountsrvs

Error:

SQL Error [42703]: ERROR: column "gaps" does not exist

I need to count in the tb_get_gap table by fields = ('LINUX', 'UNIX'), then a SUM ()in thesrvs field in the tb_getcountsrvs table by fields = 'LZ' in type, right after making this formula 100 - ((gaps / total) * 100)

Upvotes: 0

Views: 402

Answers (1)

jpock76
jpock76

Reputation: 76

It would seem that you cannot define gaps and also use it in the same query. In SQL Server you would have to use the logic twice. Maybe a subquery would work better. 

select 100 - (t.gaps / t.total) * 100)
from
(
select
    count(*) filter(WHERE tb_get_gap.system in ('LINUX','UNIX')) as gaps,
    SUM(CAST(srvs AS INT)) filter(WHERE tb_getcountsrvs.type = 'LZ') as total
FROM tb_get_gap, tb_getcountsrvs
) t

Upvotes: 1

Related Questions