GROUP BY expression must contain at least one column that is not an outer reference
group-by, sql
Solution
The problem is that the following expression is a constant:
convert(varchar(10),DATEADD(DAY,-7,GETDATE()),101),
Simply remove this from your group by, so it looks like:
GROUP BY TSM950_STATION.loc_ext_id, TSM180_CAT.cod_dtl_ds
Problem
There are several posts on this same topic, and I've read through them...but try as I might, I can't apply them to my issue. I'm sure it's something dumb I'm overlooking. What I'm trying to do is to show patients that were in a bed at a specific date and time. Each patient has an admit and discharge date, so I'm using those to determine if they were in a bed on that particular date & time. If I'm listing everything out, it works great. Now I'm trying to do a summary so it just shows 3 inpatients and 1 outpatient on a given day in this location, etc. But I'm getting the error message in the title. ``` SELECT DISTINCT TSM950_STATION.loc_ext_id AS STATION, convert(varchar(10),DATEADD(DAY,-7,GETDATE()),101) AS CENSUS_DATE, TSM180_CAT.cod_dtl_ds, COUNT(TPM300_PAT_VISIT.vst_ext_id) FROM TPM300_PAT_VISIT INNER JOIN TSM040_PERSON_HDR ON TPM300_PAT_VISIT.psn_int_id = TSM040_PERSON_HDR.psn_int_id INNER JOIN TSM950_LOCATION_REF AS TSM950_ROOM ON TPM300_PAT_VISIT.loc_lvl_4_id = TSM950_ROOM.loc_int_id INNER JOIN TSM950_LOCATION_REF AS TSM950_BED ON TPM300_PAT_VISIT.loc_lvl_5_id = TSM950_BED.loc_int_id INNER JOIN TSM950_LOCATION_REF AS TSM950_STATION ON TPM300_PAT_VISIT.loc_lvl_3_id = TSM950_STATION.loc_int_id INNER JOIN TSM180_MST_COD_DTL AS TSM180_CAT ON TPM300_PAT_VISIT.pat_cat_cd = TSM180_CAT.cod_dtl_int_id WHERE (TSM950_STATION.loc_ext_id IN ('MS', 'OB', 'SCU', 'NURS')) AND (convert(datetime,convert(varchar(10),TPM300_PAT_VISIT.adm_ts,101) +' 00:00:00'))<=convert(datetime,convert(varchar(10),DATEADD(DAY,-7,GETDATE()),101) +' 23:58:00') AND (convert(datetime,convert(varchar(10),TPM300_PAT_VISIT.dschrg_ts,101) +' 00:00:00'))>=convert(datetime,convert(varchar(10),DATEADD(DAY,-7,GETDATE()),101) +' 23:59:00') GROUP BY TSM950_STATION.loc_ext_id, convert(varchar(10),DATEADD(DAY,-7,GETDATE()),101), TSM180_CAT.cod_dtl_ds ```