From: | Susan Cassidy <susan(dot)cassidy(at)decisionsciencescorp(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | problem with query and group by error |
Date: | 2014-02-21 17:40:46 |
Message-ID: | CAE3Q8ok+WhOFnFE9995vy9Ad=iWmnEJqv3g62neWOz0zsyFACg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2014-02-21 17:50:06 | Re: problem with query and group by error |
Previous Message | Tom Lane | 2014-02-21 14:40:47 | Re: semi-variable length type |