1

I have a query in which I am trying to add a column which contains a percentage of records which have a certain value versus the total number of records. I can get the subquery to work on its own, but when I plug it into the main query it fails with an error : ERROR: subquery uses ungrouped column "ipam_addresses.host_id" from outer query

my query is:

select 
    ipam_networks.ipam_id, 
    ipam_networks.net_name, 
    ipam_networks.net_cidr, 
    zone, 
    count(*), 
    to_char(100*(select count(*) where ipam_addresses.host_id = 0) / (select count(*)), '999D99%') v 
from 
    ipam_addresses 
left outer join 
    ipam_networks 
    on ipam_addresses.parent_id = ipam_networks.ipam_id 
where 
    ipam_addresses.ip_state != 'DHCP_FREE' 
group by  
    ipam_networks.ipam_id, ipam_networks.net_name, ipam_networks.net_cidr;

Why is it throwing this error even if I specify the table name in the subquery, and what can I do to fix this?

1 Answer 1

2

You can do that using a filtered aggregate, no need for subselects at all

to_char(100 * (count(*) filter (where ipam_addresses.host_id = 0)) / count(*)::numeric, '999D99%') v 

The cast ::numeric is needed to avoid integer division

2
  • I get a syntax error at "filter"
    – john doe
    Commented Dec 11, 2019 at 18:17
  • Then you are using an outdated Postgres version
    – user1822
    Commented Dec 11, 2019 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.