column "ard.created_by" must appear in the GROUP BY clause or be used in an aggregate function

Question

Please help to solve this query.

select ard.rule_name,ard.created_by,ard.modified_by,ard.desc,ard.audit,ard.active,ard.raw_json,json_object_agg(asm.name, (asm.description, asm.keys)) as event_class_details , ard.error,ard.advanced,ard.rule_string,ard.remediation,ard.auto_assign,ard.auto_assign_to,ard.test_rule,ard.severity,ard.time_occurrence,ard.created_on,ard.modified_on, ard.source_ids from alert_rule_definition as ard left join  alert_source_mapping as asm on ( (ard.advanced = True and asm.source_id = ANY(ard.source_ids)) or (asm.source_id = ard.source_id)) where ard.rule_name in ('win_user_created_by_otherthan_admin_windows_servers_3') group by ard.rule_name;

It gives following error while execution.

column "ard.created_by" must appear in the GROUP BY clause or be used in an aggregate function

Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. — philipxy, Aug 03 '18 at 22:05
Possible duplicate of [must appear in the GROUP BY clause or be used in an aggregate function](https://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function) — philipxy, Aug 03 '18 at 22:07

score 2 · Accepted Answer · edited Aug 03 '18 at 13:46

You need to add the every column which are not used in aggregate function In your query except asm.name,asm.description,asm.keys. remaining columns should appear in group by clause try the below:

select ard.rule_name, ard.created_by, ard.modified_by, ard.desc, 
       ard.audit,ard.active,ard.raw_json,
       json_object_agg(asm.name, (asm.description, asm.keys)) as event_class_details, 
       ard.error, ard.advanced, ard.rule_string, ard.remediation, 
       ard.auto_assign, ard.auto_assign_to, ard.test_rule, ard.severity, 
       ard.time_occurrence, ard.created_on, ard.modified_on, 
       ard.source_ids 
  from alert_rule_definition as ard 
  left join alert_source_mapping as asm on ((ard.advanced = True and asm.source_id = ANY(ard.source_ids)) or (asm.source_id = ard.source_id))
 where ard.rule_name in ('win_user_created_by_otherthan_admin_windows_servers_3') 
 group by ard.rule_name, ard.created_by, ard.modified_by, ard.desc, ard.audit, ard.active, ard.raw_json, ard.error, ard.advanced, ard.rule_string,       ard.remediation, ard.auto_assign, ard.auto_assign_to, ard.test_rule, ard.severity, ard.time_occurrence, ard.created_on, ard.modified_on, ard.source_ids

column "ard.created_by" must appear in the GROUP BY clause or be used in an aggregate function

1 Answers1