Search Postgresql Archives

problem with query and group by error

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux