0

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

1 Answer 1

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.

3
  • My desire is to get a count of the all of the Dependency_device_name values (Dependency Count) and aggregate all the Dependency_name values into a single field (Dependency List) separated by commas that are associated with the same dependent_device_name (or dependent_device_fk). That is really the only result set I am looking for is those 2 values.
    – Ralph
    Apr 20, 2019 at 19:54
  • A list of dependency_device_name for each depedency_device_name? Isn't that going to be a list of one, for each one? What is the role of "effective_to"?
    – jjanes
    Apr 21, 2019 at 12:39
  • Each dependent_device_name may have 1-n number rows with a different dependency_device_name on each row. I am attempting to count the number of rows with the same dependent_device_name and build a single string of all the dependency_device_name separated by commas. The effective_to date just lets me determine if I should include that row.
    – Ralph
    Apr 21, 2019 at 14:25

Your Answer

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

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