I am trying to create a table that gives me the percentage of No/Yes from the tech_support column. I am stuck with this query that gives me the raw data, but how do I calculate the percentages of values within one column. The resulting table from the query is below in the screenshot. Im using Postgres/PGadmin4.

select c.customer_id, age, l.state, s.tech_support
from customer as c
join location as l
on c.location_id = l.location_id
join services as s
on c.customer_id = s.customer_id
where age > 25 and age < 42 and state = 'NC'

Table Result

CodePudding user response：

You can use conditional aggregation combining SUM() with CASE. For example, to get the ratio of 'Yes' over the total number of rows you can do:

with
q as (
  -- your query here
)
select
  1.0 * sum(case when tech_support = 'Yes' then 1 else 0 end) 
  /
  count(*)
from q

EDIT:

If you want to just get the ratio and not the rest of the data you can do:

select 1.0 * sum(case when tech_support = 'Yes' then 1 else 0 end)
  / count()
from customer as c
join location as l
on c.location_id = l.location_id
join services as s
on c.customer_id = s.customer_id
where age > 25 and age < 42 and state = 'NC'

If you want to keep all the data "as is" and want to add the ratio as an extra column for all rows you can use OVER(). For example:

select c.customer_id, age, l.state, s.tech_support,
  1.0 * sum(case when tech_support = 'Yes' then 1 else 0 end) over()
  / count() over()
from customer as c
join location as l
on c.location_id = l.location_id
join services as s
on c.customer_id = s.customer_id
where age > 25 and age < 42 and state = 'NC'