Another - ERROR: column "da2.dependency_device_name" must appear in the GROUP BY clause or be used in an aggregate function

Question

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

Answer 1

It needs to be in the GROUP by of the "xy1" subselect, not of the outer query.

But it probably really needs to be taken out of the select list of "xy1" and not put in the GROUP BY. Having it in both the string_agg and the GROUP BY would be legal, but seems rather pointless.

Where the error message says "or be used in an aggregate function", it means "or be used only in an aggregate function"

There many ways to form a legal query, but the question is, which of them does what you want?

It seems like you want your subquery to be this:

Select 
  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
  where effective_to is null
  group by da2.dependent_device_name, da2.dependent_device_fk

And then you would not want the "effective_to" in your join condition, having filtered them out already.