CODEWITHSUNDEEP
sqldatabasepostgresqlgroup-bycount
Luis Henrique
Luis Henrique

Reputation: 771

Generate percentage metrics between two queries

How to generate percentage metrics between two queries on the same table with different conditions ?

Example:

_________________________________________
            |        |         |
environment | online | offline | metrics
____________|________|_________|_________

  web          3         1         75%
  docker       2         1         50%

Query 1:

Select environment, count(available) from tb_infra where available = 'ON' group by environment order by count desc

Query 2:

Select environment, count(available) from tb_infra where available = 'OFF' group by environment order by count desc

Upvotes: 1

Views: 54

Answers (1)

GMB
GMB

Reputation: 222582

You can do conditional aggregation. The standard filter clause to aggregate functions, which Postgres supports, comes handy for this:

select
    environment,
    count(*) filter(where available = 'ON')  online,
    count(*) filter(where available = 'OFF') offline,
    avg((available = 'ON')::int) metrics
from tb_infra 
group by environment

In this resultset, metrics is a value between 0 and 1 that represents the ratio of 'ON' records. You can easily turn that to a percentage by multiplying it by 100.

Upvotes: 2

Related Questions