CODEWITHSUNDEEP
postgresqlsubquerypercentage
nomnom3214
nomnom3214

Reputation: 259

How to get a percentage from two different columns in postgres?

My table is in postgre and I have this table1 in my database:

category  status   value
type a     open      4
type a     close     5
type b     open      3
type b     close     5
type c     open      2
type c     close     4

and I want to calculate the percentage of open status at each category. The formula is: % type x (open) = (open / open + close) * 100

with the query, I expect to get:

category    percentage
type a       44,44%
type b       60%
type c       50%

How can I get the desired result with query?

Thanks in advance.

Upvotes: 0

Views: 1081

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247950

I think aggregates would be most efficient:

SELECT category,
       100.0 *  -- use a decimal point to force floating point arithmetic
       sum(value) FILTER (WHERE status = 'open') /
       nullif(sum(value), 0) AS percentage  -- avoid division by zero
FROM your_table
GROUP BY category;

Upvotes: 1

chvndb
chvndb

Reputation: 745

You can create a window that partitions your data on category as follows:

window w as ( partition by category )

Then you can aggregate over that window to get the number of open per category using the defined window:

sum(value) filter (where status = 'open') over w

In the same way you get the total per category using the defined window, the nullif is there to avoid division by 0:

nullif(sum(value) over w, 0)

To put it all together:

select distinct on (category)
      category,
      100 * sum(value) filter (where status = 'open') over w / nullif(sum(value) over w, 0) as percentage
from your_table
window w as ( partition by category );

As we are using window functions and not a grouping, we need to remove duplicates by adding the distinct on (category)

Upvotes: 1

Related Questions