Group by statement problem

I am using the T-SQL code below to pull patient information. The code returns 86 rows, however, there are only 9 distinct account numbers. Why is the group by statement not grouping these together to only display the 9 distinct accounts and associated data?

select
srm.episodes.episode_type as Visit_Type,
srm.episodes.account_number as Account_Number,
srm.episodes.medrec_no as MRN,
dbo.PtMstr.PatientFullName,
left(srm.episodes.admission_date,11) as Admit_Date,
left(srm.episodes.episode_date,11) as Disch_Date,
dbo.PtMstr.Cases as Cases,
dbo.PtMstr.TotCharges,
srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,
srm.cdmab_base_info.adm_dx_adt as Admitting_Dx
,srm.event_types.event_type_code
from srm.cdmab_base_info inner join
srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key
inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key
inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key
inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number
where srm.cdmab_base_info.abst_cmp_status <> 'Y'
and srm.episodes.episode_date is not null
and srm.event_types.event_type_code <> 'ACOD'
AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate
AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)
Group By srm.episodes.account_number,
dbo.PtMstr.TotCharges,
srm.episodes.episode_type,
srm.episodes.medrec_no,
dbo.PtMstr.PatientFullName,
srm.episodes.admission_date,
srm.episodes.episode_date,
dbo.PtMstr.Cases,
srm.cdmab_base_info.abst_cmp_status,
srm.cdmab_base_info.adm_dx_adt,
srm.event_types.event_type_code

Use the following query..

select

srm.episodes.episode_type as Visit_Type,

srm.episodes.account_number as Account_Number,

srm.episodes.medrec_no as MRN,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases as Cases,

dbo.PtMstr.TotCharges,

srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,

srm.cdmab_base_info.adm_dx_adt as Admitting_Dx

,srm.event_types.event_type_code

from

srm.cdmab_base_info

inner join srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key

inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key

inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key

inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number

where

srm.cdmab_base_info.abst_cmp_status <> 'Y'

and srm.episodes.episode_date is not null

and srm.event_types.event_type_code <> 'ACOD'

AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate

AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)

Group By

srm.episodes.account_number,

dbo.PtMstr.TotCharges,

srm.episodes.episode_type,

srm.episodes.medrec_no,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases,

srm.cdmab_base_info.abst_cmp_status,

srm.cdmab_base_info.adm_dx_adt,

srm.event_types.event_type_code

|||

I had to remove the AS portion of the group by clause to get the code to work , however, it still returns 86 rows versus the expected 9 distinct rows.

|||

How you know there is only 9 distinct record. You only the get the number of rows as per the following query..& i didn't understand your requirement on your query(there is no group by funcations used).

select Distinct

srm.episodes.episode_type as Visit_Type,

srm.episodes.account_number as Account_Number,

srm.episodes.medrec_no as MRN,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases as Cases,

dbo.PtMstr.TotCharges,

srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,

srm.cdmab_base_info.adm_dx_adt as Admitting_Dx

,srm.event_types.event_type_code

from

srm.cdmab_base_info

inner join srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key

inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key

inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key

inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number

where

srm.cdmab_base_info.abst_cmp_status <> 'Y'

and srm.episodes.episode_date is not null

and srm.event_types.event_type_code <> 'ACOD'

AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate

AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)

|||

I appreciate you help. I ordered the data by account number and saw there were 9 distinct account numbers. I also noticed the srm.event_types.event_type_code field should not have been in this query; once I removed it, the code returned the expected 9 rows of data using either of the examples you provided. Thanks again for your assistance.

|||

You are grouping several additional columns after the account number.

If you just want the nine accounts listed, you'll need to just group on that column.

Then you can apply aggregates to get sums, etc. of the other data you desire.