select query in subquery giving Column is invalid in the select list because it is not contained error

Question

I have following query which i want to get sum counts for my data

SELECT  
  TI.[text] as zone,
  YEAR (ER.Inserted) as [Year], 
  SUM(CONVERT(INT,DRT.RDRT)) as RDRT,
  SUM(CONVERT(INT,DRT.FACT)) as FACT ,
  SUM(CONVERT(INT,DRT.ERU))  as ERU,
  (
     SELECT COUNT(ER1.ReportID) 
     FROM dbo.EW_Reports ER1
     INNER JOIN  dbo.EW_Report_InformationManagement ERI ON ER1.ReportID = ERI.ReportID
     INNER JOIN EW_Report_Country ERC1 ON   ER1.ReportID = ERC1.ReportID 
     INNER JOIN ApplicationDB.dbo.Country C1 ON ERC1.CountryID = C1.countryId 
     INNER JOIN ApplicationDB.dbo.Region R1 ON C1.regionId = R1.regionId
     INNER JOIN ApplicationDB.dbo.Zone Z1  ON R1.zoneId = Z1.zoneId 
     WHERE ERI.EmergencyAppeal IS NOT NULL
      AND (YEAR ( ER1.Inserted) = YEAR ( ER.Inserted))
      AND Z1.zoneId =  Z.zoneId     
   ) as emergencyAppeals    
FROM     EW_Reports ER 
INNER JOIN EW_DisasterResponseTools DRT ON   ER.ReportID = DRT.ReportID 
INNER JOIN EW_Report_Country ERC ON   ER.ReportID = ERC.ReportID  
INNER JOIN ApplicationDB.dbo.Country C ON ERC.CountryID = c.countryId 
INNER JOIN ApplicationDB.dbo.Region R ON c.regionId = R.regionId
INNER JOIN ApplicationDB.dbo.Zone Z ON R.zoneId = Z.zoneId 
INNER JOIN ApplicationDB.dbo.Translation T ON Z.translationId = T.translationId
INNER JOIN ApplicationDB.dbo.TranslationItem TI ON T.translationId = TI.translationId
INNER JOIN EW_lofDisasterTypes D ON ER.DisasterTypeID = D.TranslationID AND D.LanguageID = 1 AND TI.languageID = 1      
WHERE (YEAR ( ER.Inserted) IN (2011,2012))
GROUP BY TI.[text], YEAR (ER.Inserted)

But its giving following error

Column 'ApplicationDB.dbo.Zone.zoneId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please assist me how to resolve this error .

Low Chee Mun · Accepted Answer · 2013-10-02T02:11:08.523

0

there are too many ApplicationDB.dbo.Zone.zoneId records in your table already

simple add ApplicationDB.dbo.Zone.zoneId in group by then problem will solved

Select ....
.....
GROUP BY TI.[text], YEAR (ER.Inserted) ,ApplicationDB.dbo.Zone.zoneId

For your question why u need to add ApplicationDB.dbo.Zone.zoneId in my group as i am using that in ur subquery, this is because u perform a outer condition in your subquery

SELECT
----
(
SELECT
-----
INNER JOIN ApplicationDB.dbo.Zone Z1  ON R1.zoneId = Z1.zoneId 
WHERE
----
AND Z1.zoneId =  Z.zoneId 
)
-----
INNER JOIN ApplicationDB.dbo.Zone Z ON R.zoneId = Z.zoneId
WHERE (YEAR ( ER.Inserted) IN (2011,2012)) 
------

note that you have a condition in different years

so your data flow may like this

ZoneID    Years     Record
  1       2011        1000
  1       2012        1000

same zone id contain different years, without proper grouping, sql got no way to group years column

edited Oct 02 '13 at 02:11

answered Oct 01 '13 at 09:39

Low Chee Mun

610
1
4
9

thanks its solved my problem but can you please tell me why i need to add ApplicationDB.dbo.Zone.zoneId in my group as i am using that in my subquery ? – rpulekar Oct 01 '13 at 09:51
answer updated, for better explanation, maybe u can take a look on this , http://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e – Low Chee Mun Oct 02 '13 at 02:11
No, this is not the answer, because adding ApplicationDB.dbo.Zone.zoneId has physical result in grouping by this column. Yes, adding ApplicationDB.dbo.Zone.zoneId remove error, but thw select result could by wrong. – bmi Mar 01 '22 at 13:55

score 0 · Answer 2 · answered Oct 01 '13 at 09:43

0

it is showing the error because you have used this in sub query where condition

you need toadd ApplicationDB.dbo.Zone.zoneId in group by

answered Oct 01 '13 at 09:43

Nitu Bansal

3,826
3
18
24

select query in subquery giving Column is invalid in the select list because it is not contained error

2 Answers2