3

I'm using a case statement to calculate two columns, primary_specialty and secondary_specialty. This works well, however, I'd like to then perform a GROUP BY on the pd.id and receive the following error:

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

The desired output is a single row for each pd with a column for primary_specialty and secondary_specialty

SELECT pd.id,
       pd.npi,
       pppd.created_at AS "date_submitted", 
       pppd.converted_at AS "date_approved", 
       dp.created_at AS "date_profile_created",
       t.description AS "npi_specialty",
       case when ds.ordinal = 1 then s.name end as "primary_specialty",
       case when ds.ordinal = 2 then s.name end as "secondary_specialty"

FROM potential_doctors AS pd
     INNER JOIN patient_profile_potential_doctors as pppd on pd.id = pppd.potential_doctor_id
     INNER JOIN doctor_taxonomies AS dt on pd.id = dt.potential_doctor_id
     INNER JOIN taxonomies AS t on dt.taxonomy_id = t.id
     INNER JOIN doctor_profiles AS dp on pd.npi = dp.npi
     INNER JOIN doctor_specialties AS ds on dp.id = ds.doctor_profile_id
     INNER JOIN specialties AS s on ds.specialty_id = s.id
user2954587
  • 4,661
  • 6
  • 43
  • 101

3 Answers3

4

Use DISTINCT ON (pd.id) instead of GROUP BY

SELECT DISTINCT ON (pd.id) pd.id,
           pd.npi,
           pppd.created_at AS "date_submitted", 
           pppd.converted_at AS "date_approved", 
           dp.created_at AS "date_profile_created",
           t.description AS "npi_specialty",
           case when ds.ordinal = 1 then s.name end as "primary_specialty",
           case when ds.ordinal = 2 then s.name end as "secondary_specialty"

    FROM potential_doctors AS pd
         INNER JOIN patient_profile_potential_doctors as pppd on pd.id = pppd.potential_doctor_id
         INNER JOIN doctor_taxonomies AS dt on pd.id = dt.potential_doctor_id
         INNER JOIN taxonomies AS t on dt.taxonomy_id = t.id
         INNER JOIN doctor_profiles AS dp on pd.npi = dp.npi
         INNER JOIN doctor_specialties AS ds on dp.id = ds.doctor_profile_id
         INNER JOIN specialties AS s on ds.specialty_id = s.id
Sergey Gershkovich
  • 352
  • 2
  • 6
2

This may be the query that you want:

SELECT pd.id, pd.npi, pppd.created_at AS "date_submitted", pppd.converted_at AS "date_approved", 
       dp.created_at AS "date_profile_created",
       t.description AS "npi_specialty",
       max(case when ds.ordinal = 1 then s.name end) as "primary_specialty",
       max(case when ds.ordinal = 2 then s.name end) as "secondary_specialty"
FROM potential_doctors AS pd
     INNER JOIN patient_profile_potential_doctors as pppd on pd.id = pppd.potential_doctor_id
     INNER JOIN doctor_taxonomies AS dt on pd.id = dt.potential_doctor_id
     INNER JOIN taxonomies AS t on dt.taxonomy_id = t.id
     INNER JOIN doctor_profiles AS dp on pd.npi = dp.npi
     INNER JOIN doctor_specialties AS ds on dp.id = ds.doctor_profile_id
     INNER JOIN specialties AS s on ds.specialty_id = s.id
GROUP BY pd.id, pd.npi, pppd.created_at,  pppd.converted_at, t.description
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

If you group by one or more column, you can only select those columns or an aggregated function.

Because you are grouping many rows, you could have many "primary_specialty" for each pd.id. If you assume though, from your database schema, that you will have only one distinct value in the set, you could use an aggregated function (like MAX) to get the value you want. Something like this:

SELECT pd.id,
       MAX(case when ds.ordinal = 1 then s.name end) as "primary_specialty",
       MAX(case when ds.ordinal = 2 then s.name end) as "secondary_specialty"
Alex Zen
  • 906
  • 7
  • 9