I am attempting to aggregate dependency_device_name
into one string, "Dependency List". Here is the error:
ERROR: column "da2.dependency_device_name" must appear in the GROUP BY clause or be used in an aggregate function.
Here is the sql. I have added the field to the group by and still have the same error message:
Select
xy1."Dependency Count",
xy1."Dependency List",
ag2.report_type_name,
xy1.dependent_device_name
from
view_affinitygroup_v2 ag2
left join
(
Select
da2.dependency_device_name,
da2.effective_to,
da2.dependent_device_name,
da2.dependent_device_fk,
sum(1) "Dependency Count",
string_agg(da2.dependency_device_name,', ') "Dependency List"
from
view_deviceaffinity_v2 da2
) as xy1
on xy1.dependent_device_fk = ag2.primary_device_fk and xy1.effective_to is null
where ag2.report_type_id = 1
group by
da2.dependency_device_name,
ag2.report_type_name,
xy1.dependent_device_name,
xy1.effective_to