Susan Cassidy-3 wrote > I have a large query: > SELECT distinct on (srs.scan_run_id) srs.scan_run_id, > srs.run_request_number, srs.container_id, srs.manifest_id, > srs.scan_system_name_id, > srs.scan_site_name_id, srs.scan_site_nickname_id, > to_char(srs.start_time, 'MM/DD/YY HH24:MI:SS'), > to_char(srs.stop_time, 'MM/DD/YY HH24:MI:SS'), srs.system_operator, > srs.system_baseline_configuration_file_version_id, > srs.container_contents, srs.container_run_truth_data, > srs.scan_type_id, sty.scan_type, > srs.hardware_version_or_hardware_identifier_id, > srs.software_version_id, srs.operator_notes, > to_char(srs.expiration_date, 'MM/DD/YY HH24:MI:SS'), > srs.scan_outcome_id, > to_char(srs.alarm_time, 'MM/DD/YY HH24:MI:SS'), > srs.alarm_decision_id, srs.material_detected_id, srs.data_access, > ssn.scan_system_name, ssn.simulation, ssitenames.scan_site_name, > ssitenicknames.scan_site_nickname, > hvhi.hardware_version_or_hardware_identifier_name, > sv.software_version, sc.description > from scan_run_summary srs left outer join scan_system_names ssn on > srs.scan_system_name_id = ssn.scan_system_name_id > left outer join scan_site_names ssitenames on > srs.scan_site_name_id = ssitenames.scan_site_name_id > left outer join scan_site_nicknames ssitenicknames on > srs.scan_site_nickname_id = > ssitenicknames.scan_site_nickname_id > left outer join hardware_version_or_hardware_identifiers hvhi on > srs.hardware_version_or_hardware_identifier_id = > hvhi.hardware_version_or_hardware_identifier_id > left outer join software_versions sv on srs.software_version_id = > sv.software_version_id > left outer join scenes sc on srs.container_run_truth_data = > sc.scene_id > left outer join scan_types sty on srs.scan_type_id = > sty.scan_type_id > join scene_thing_instances sti on srs.container_run_truth_data = > sti.scene_id > join scene_things stg on sti.scene_thing_id = stg.scene_thing_id > group by srs.scan_run_id > ; > > > That gives this error: > > ERROR: column "sty.scan_type" must appear in the GROUP BY clause or be > used in an aggregate function > LINE 5: srs.scan_type_id, sty.scan_type, srs.hardware_version... > > I don't see why sty.scan_type should be singled out as requiring a group > by > clause, when there are many other columns specified. > > If I add scan_type to the group by, then it gives the same error, but with > ssn.scan_system_name. > > Am I going to have to specify all the columns in the group by clause? > > I originally had the query without the group by, but I had duplicate rows, > so I added a group by to eliminate them. > > Thanks, > Susan Newer releases (not sure which) are capable of identifying a primary key in a group by and allow you to omit all dependent columns of said primary key. However, as a general rule, every non-aggregated column must appear in the GROUP BY. There is not "default behavior" for columns not appearing in group by nor that have been aggregated. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/problem-with-query-and-group-by-error-tp5793127p5793128.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
