0

I am a network guy so I apologize for this.... I have two tables I need to join and pull names out of. pt_basic has the patient names and codes I need, ptc_diagnosis has their diagnosis. I need to pull the names of patients who have one and only one diagnosis. I can join the tables on patient_id. here is what i wrote:

select 
PTC_DIAGNOSIS.PATIENT_ID, 
PT_BASIC.PATIENT_CODE, 
PT_BASIC.NAME_FIRST, 
PT_BASIC.NAME_LAST 
from PTC_DIAGNOSIS 
inner join PT_BASIC on PTC_DIAGNOSIS.PATIENT_ID=PT_BASIC.PATIENT_ID
where PTC_DIAGNOSIS.create_date>'12/01/2015' 
group by PTC_DIAGNOSIS.patient_id 
having COUNT(*)=1

I get the error Column 'PT_BASIC.NAME_FIRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I have moved columns around, tried having pt_basic first and re-written it 15 different ways with similar errors every time. i feel i am close, but maybe not.

thank you

  • What RDBMS are you using? If this is SQL Server then your error message contains the answer you're looking for. – Erik Apr 6 '16 at 15:12
2

very close only the GROUP BY Columns forgotten

Try this:

select 
PTC_DIAGNOSIS.PATIENT_ID, 
PT_BASIC.PATIENT_CODE, 
PT_BASIC.NAME_FIRST, 
PT_BASIC.NAME_LAST 
from PTC_DIAGNOSIS 
inner join PT_BASIC on PTC_DIAGNOSIS.PATIENT_ID=PT_BASIC.PATIENT_ID
where PTC_DIAGNOSIS.create_date>'12/01/2015' 
group by 
PTC_DIAGNOSIS.PATIENT_ID, 
PT_BASIC.PATIENT_CODE, 
PT_BASIC.NAME_FIRST, 
PT_BASIC.NAME_LAST 
having COUNT(*)=1
  • thank you, but it didn't quite return what I needed. I get patients with more than one diagnosis. i think we could take out the where clause as that date really doesn't matter as long as i can get patients with only one diagnosis. – Scott Jones Apr 6 '16 at 14:00
  • taking the where clause out seems to be giving me the right information now, I just have to find a way to narrow the patients to a time frame. i may have to join another table to the mix that has admission dates – Scott Jones Apr 6 '16 at 14:13
  • ok i added this to help narrow the results, but whenever i have any date column in the query, it is ignored. for example, i have the last cert column in the one i wrote below but it returns patients with certifications from 2014 and 2013. if i take the date column out the query runs great, just with too much info – Scott Jones Apr 6 '16 at 15:01
  • select PTC_DIAGNOSIS.PATIENT_ID, PT_BASIC.PATIENT_CODE, PT_BASIC.NAME_FIRST, PT_BASIC.NAME_LAST from PTC_DIAGNOSIS inner join PT_BASIC on PTC_DIAGNOSIS.PATIENT_ID=PT_BASIC.PATIENT_ID where PT_BASIC.DATASET_ID='26' or PT_BASIC.DATASET_ID='31' and PT_BASIC.last_certification_DATE>'2015-03-01 00:00:00' group by PTC_DIAGNOSIS.PATIENT_ID, PT_BASIC.PATIENT_CODE, PT_BASIC.NAME_FIRST, PT_BASIC.NAME_LAST having COUNT(*)=1 – Scott Jones Apr 6 '16 at 15:03
  • what column type is the PT_BASIC.last_certification_DATE? if timestamp or date you can use the Date/Time Functions or append ::date to the column (to "switch" the type) Like select now(); = full timestamp or select now()::date= date only or `select now()::date -1' = yesterday and so on... – Patrick7 Apr 6 '16 at 17:52
2

As Patrick7 has already said, you need your GROUP BY to include all the non-aggregated columns that are in your SELECT list.

In your case, however, there is a way to avoid duplicating all the SELECT columns in GROUP BY. You could first aggregate the PTC_DIAGNOSIS rows separately:

SELECT
  patient_id
FROM
  PTC_DIAGNOSIS 
WHERE
  create_date > '20151201'     -- or '20150112', whatever that '12/01/2015' means
GROUP BY
  patient_id
HAVING
  COUNT(*)=1

then use that query as a derived table and join it to PT_BASIC:

SELECT
  p.PATIENT_ID,
  p.PATIENT_CODE, 
  p.NAME_FIRST, 
  p.NAME_LAST 
FROM
  (
    SELECT
      PATIENT_ID
    FROM
      PTC_DIAGNOSIS 
    WHERE
      create_date > '20151201'     -- or '20150112', whatever that '12/01/2015' means
    GROUP BY
      patient_id
    HAVING
      COUNT(*)=1
  ) AS d
  INNER JOIN PT_BASIC AS p ON d.PATIENT_ID = p.PATIENT_ID
;

The result would be the same as with Patrick's query, and this way you can include as many PT_BASIC columns into the output as you like without having to duplicate them in another clause.

Additional notes:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.