MSSQL Multiple Join Group By

Question

 Select TOP 50
 C1.TCKimlikNo as TCKimlikNo,
 GSSFat.FatTar as GssFatTar,
 KID.gssGidisNo,
 C1.UNVAN as HastaAdi,
 K.NO as MasterNo,
 K.HastaNO as HastaNo,
 Cast(KID.IS_TARIHI + ( 0.0417 * DatePart(Hour, KID.PostTime)) + ( 0.000695 * DatePart(Minute, KID.PostTime)) as DateTime) as TarihSaat,
 I.AD as IslemAdi,
 case IsNull(C1.CINSIYET, '') when 'K' then 'Kadın' else 'Erkek' end as Cinsiyet,
 C1.DOGUM_TAR as HastaDogTar,
 C1.EVTEL as Telefon,
 C1.GSM1 as Gsm1,
 C1.ADRES1 as Adres,
 C1.BABA_AD as BabaAdi,
 Ilcx.AD as AdresIlce,
 Ilx.AD as AdresIl,
 DatePart(Year, KID.IS_TARIHI) - IsNull(DatePart(Year, C1.DOGUM_TAR), 0) as HastaYasi,
 IsNull(K.Aciklama, '') + ' ' + IsNull(K.Aciklama2, '') + ' ' + IsNull(K.Aciklama3, '') as Aciklama,
 K.Aciklama as Aciklama_1,
 K.Aciklama2 as Aciklama_2,
 K.Aciklama3 as Aciklama_3,
 KT.AD as VezneAdi



 from KASA_ISLEM_DETAY as KID with(nolock)  
 Inner Join KASA as K with(nolock) on  KID.KASANO = K.NO 
 Inner Join CARI as C1 with(nolock) on  K.HastaNo = C1.NO 
 Inner Join ISLEM as I with(nolock) on  KID.IslemNo = I.NO 
 Left Join vw_KasaOdeme as OD with(nolock) on  KID.KASANO = OD.MasterNO 
 Left Outer Join CARI as C with(nolock) on  K.AnlasKurumNo = C.NO and C.SAHIS_KURUM = 0  
 Left Outer Join PERSONEL as P with(nolock) on  KID.DOCTOR_NO = P.NO 
 Left Outer Join (Select Max(LISTE_ISLEM_KOD) as IslemKod, ISLEM_NO from LISTE_FIYAT Group By ISLEM_NO) as LF on KID.IslemNo = LF.ISLEM_NO 
 Left Outer Join IL as Ilx with(nolock) on  C1.IL_NO = Ilx.NO 
 Left Outer Join ILCE as Ilcx with(nolock) on  C1.ILCE_NO = Ilcx.NO 
 Left Outer Join KASA_TANIM as KT with(nolock) on  K.KASA_NO = KT.No 
 Left Join vw_gssGiden as GSSProv on KID.gssGidisNo = GSSProv.TakipNo 
 Left Join vw_gssFatura as GSSFat on GSSProv.FatTakipNo = GSSFat.FatTakipNo 
 Left Outer Join Uyruk as UY with(nolock) on C1.Uyrugu = UY.NO Where K.SubeNo = 0  and GSSFat.FatTar between '05.012.2007' and '05.05.2016' and K.DELETED = 0   and (KID.IslemNo = 12155 or KID.IslemNo = 12154) and GSSFat.FatTar is not NULL and KID.gssGidisNo is not null

 GROUP BY K.HastaNo
 ORDER BY HastaNo ASC, TarihSaat DESC

I want to be unique results

1 Results of the most recent date need to get a result

Microsof SQL Server Studio

Error Code

Msg 8120, Level 16, State 1, Line 2 Column 'CARI.TCKimlikNo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Like the error message says, either remove 'CARI.TCKimlikNo' from the SELECT List, or put it in an aggregate function, or add it to the GROUP BY clause. And by the way, after you do it for that column, you'll have to do it for all the other columns that aren't in the GROUP BY. Probably you should google and learn about the GROUP BY clause in SQL, because you seem to have a basic misunderstanding of it. — Tab Alleman, May 05 '16 at 12:50
me the code "K.HastaNo" you can write to give unique results? — ömer özer, May 05 '16 at 13:06

score 0 · Answer 1 · answered May 05 '16 at 13:09

Add C1.TCKimlikNo in GROUP BY Clause

 Select TOP 50
 C1.TCKimlikNo as TCKimlikNo,
 GSSFat.FatTar as GssFatTar,
 KID.gssGidisNo,
 C1.UNVAN as HastaAdi,
 K.NO as MasterNo,
 K.HastaNO as HastaNo,
 Cast(KID.IS_TARIHI + ( 0.0417 * DatePart(Hour, KID.PostTime)) + ( 0.000695 * DatePart(Minute, KID.PostTime)) as DateTime) as TarihSaat,
 I.AD as IslemAdi,
 case IsNull(C1.CINSIYET, '') when 'K' then 'Kadın' else 'Erkek' end as Cinsiyet,
 C1.DOGUM_TAR as HastaDogTar,
 C1.EVTEL as Telefon,
 C1.GSM1 as Gsm1,
 C1.ADRES1 as Adres,
 C1.BABA_AD as BabaAdi,
 Ilcx.AD as AdresIlce,
 Ilx.AD as AdresIl,
 DatePart(Year, KID.IS_TARIHI) - IsNull(DatePart(Year, C1.DOGUM_TAR), 0) as HastaYasi,
 IsNull(K.Aciklama, '') + ' ' + IsNull(K.Aciklama2, '') + ' ' + IsNull(K.Aciklama3, '') as Aciklama,
 K.Aciklama as Aciklama_1,
 K.Aciklama2 as Aciklama_2,
 K.Aciklama3 as Aciklama_3,
 KT.AD as VezneAdi



 from KASA_ISLEM_DETAY as KID with(nolock)  
 Inner Join KASA as K with(nolock) on  KID.KASANO = K.NO 
 Inner Join CARI as C1 with(nolock) on  K.HastaNo = C1.NO 
 Inner Join ISLEM as I with(nolock) on  KID.IslemNo = I.NO 
 Left Join vw_KasaOdeme as OD with(nolock) on  KID.KASANO = OD.MasterNO 
 Left Outer Join CARI as C with(nolock) on  K.AnlasKurumNo = C.NO and C.SAHIS_KURUM = 0  
 Left Outer Join PERSONEL as P with(nolock) on  KID.DOCTOR_NO = P.NO 
 Left Outer Join (Select Max(LISTE_ISLEM_KOD) as IslemKod, ISLEM_NO from LISTE_FIYAT Group By ISLEM_NO) as LF on KID.IslemNo = LF.ISLEM_NO 
 Left Outer Join IL as Ilx with(nolock) on  C1.IL_NO = Ilx.NO 
 Left Outer Join ILCE as Ilcx with(nolock) on  C1.ILCE_NO = Ilcx.NO 
 Left Outer Join KASA_TANIM as KT with(nolock) on  K.KASA_NO = KT.No 
 Left Join vw_gssGiden as GSSProv on KID.gssGidisNo = GSSProv.TakipNo 
 Left Join vw_gssFatura as GSSFat on GSSProv.FatTakipNo = GSSFat.FatTakipNo 
 Left Outer Join Uyruk as UY with(nolock) on C1.Uyrugu = UY.NO Where K.SubeNo = 0  and GSSFat.FatTar between '05.012.2007' and '05.05.2016' and K.DELETED = 0   and (KID.IslemNo = 12155 or KID.IslemNo = 12154) and GSSFat.FatTar is not NULL and KID.gssGidisNo is not null

 GROUP BY K.HastaNo,C1.TCKimlikNo
 ORDER BY HastaNo ASC, TarihSaat DESC

TEST : Msg 8120, Level 16, State 1, Line 3 Column 'vw_gssFatura.FatTar' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. — ömer özer, May 05 '16 at 13:10

score 0 · Answer 2 · edited May 23 '17 at 12:16

In general, all fields not being aggregated (with sum, max, min, avg, count, etc..) in your SELECT directive should be in the GROUP BY. As all items in your SELECT are not being aggregated, then you should put all of them in GROUP BY. Take a look at this question.

But, for what you want, would be better use SELECT DISTINCT instead of aggregating, like the following code:

Select DISTINCT TOP 50
 C1.TCKimlikNo as TCKimlikNo,
 GSSFat.FatTar as GssFatTar,
 KID.gssGidisNo,
 C1.UNVAN as HastaAdi,
 K.NO as MasterNo,
 K.HastaNO as HastaNo,
 Cast(KID.IS_TARIHI + ( 0.0417 * DatePart(Hour, KID.PostTime)) + ( 0.000695 * DatePart(Minute, KID.PostTime)) as DateTime) as TarihSaat,
 I.AD as IslemAdi,
 case IsNull(C1.CINSIYET, '') when 'K' then 'Kadın' else 'Erkek' end as Cinsiyet,
 C1.DOGUM_TAR as HastaDogTar,
 C1.EVTEL as Telefon,
 C1.GSM1 as Gsm1,
 C1.ADRES1 as Adres,
 C1.BABA_AD as BabaAdi,
 Ilcx.AD as AdresIlce,
 Ilx.AD as AdresIl,
 DatePart(Year, KID.IS_TARIHI) - IsNull(DatePart(Year, C1.DOGUM_TAR), 0) as HastaYasi,
 IsNull(K.Aciklama, '') + ' ' + IsNull(K.Aciklama2, '') + ' ' + IsNull(K.Aciklama3, '') as Aciklama,
 K.Aciklama as Aciklama_1,
 K.Aciklama2 as Aciklama_2,
 K.Aciklama3 as Aciklama_3,
 KT.AD as VezneAdi



 from KASA_ISLEM_DETAY as KID with(nolock)  
 Inner Join KASA as K with(nolock) on  KID.KASANO = K.NO 
 Inner Join CARI as C1 with(nolock) on  K.HastaNo = C1.NO 
 Inner Join ISLEM as I with(nolock) on  KID.IslemNo = I.NO 
 Left Join vw_KasaOdeme as OD with(nolock) on  KID.KASANO = OD.MasterNO 
 Left Outer Join CARI as C with(nolock) on  K.AnlasKurumNo = C.NO and C.SAHIS_KURUM = 0  
 Left Outer Join PERSONEL as P with(nolock) on  KID.DOCTOR_NO = P.NO 
 Left Outer Join (Select Max(LISTE_ISLEM_KOD) as IslemKod, ISLEM_NO from LISTE_FIYAT Group By ISLEM_NO) as LF on KID.IslemNo = LF.ISLEM_NO 
 Left Outer Join IL as Ilx with(nolock) on  C1.IL_NO = Ilx.NO 
 Left Outer Join ILCE as Ilcx with(nolock) on  C1.ILCE_NO = Ilcx.NO 
 Left Outer Join KASA_TANIM as KT with(nolock) on  K.KASA_NO = KT.No 
 Left Join vw_gssGiden as GSSProv on KID.gssGidisNo = GSSProv.TakipNo 
 Left Join vw_gssFatura as GSSFat on GSSProv.FatTakipNo = GSSFat.FatTakipNo 
 Left Outer Join Uyruk as UY with(nolock) on C1.Uyrugu = UY.NO Where K.SubeNo = 0  and GSSFat.FatTar between '05.012.2007' and '05.05.2016' and K.DELETED = 0   and (KID.IslemNo = 12155 or KID.IslemNo = 12154) and GSSFat.FatTar is not NULL and KID.gssGidisNo is not null
ORDER BY HastaNo ASC, TarihSaat DESC

It gives multiple results `ZKMP69 GÜLŞEN KARAGÖZ 91625 ZKMP69 GÜLŞEN KARAGÖZ 91625 ZKMP69 GÜLŞEN KARAGÖZ 91625 L3GMBL HİKMET ERGÜL 41103 L3GMBL HİKMET ERGÜL 41103 L3GMBL HİKMET ERGÜL 41103` — ömer özer, May 06 '16 at 07:10

MSSQL Multiple Join Group By

2 Answers2