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?