Msg 156, Level 15, State 1, Line 22 Incorrect Syntax Near The Keyword 'having'

SELECT CCDMS_Company.CompanyID, CCDMS_Company.CompanyName, CCDMS_Container.ContainerID, CCDMS_Container.Price, SUM(CASE WHEN CCDMS_Container.ContainerSize = 1

Solution 1:

You can not use alias in having. Use below instead, or use CTE for simplicity

select CCDMS_Company.CompanyID , CCDMS_Company.CompanyName ,
CCDMS_Container.ContainerID ,CCDMS_Container.Price , 
sum(casewhen CCDMS_Container.ContainerSize=1then1else0end)as size1,
sum(casewhen CCDMS_Container.ContainerSize=2then1else0end)as size2,
sum(casewhen CCDMS_Container.ContainerSize=3then1else0end)as size3,
sum(casewhen CCDMS_Container.ContainerSize=4then1else0end)as size4,
sum(casewhen CCDMS_Container.ContainerSize=5then1else0end)as size5
   from 
CCDMS_Company
 innerjoin
CCDMS_Container
on CCDMS_Container.Co_ID = CCDMS_Company.CompanyID
and 
CCDMS_Company.CityID =1groupby 
CCDMS_Company.CompanyID,CCDMS_Company.CompanyName,CCDMS_Container.ContainerID,CCDMS_Container.Price

havingsum(casewhen CCDMS_Container.ContainerSize=1then1else0end) >=2andsum(casewhen CCDMS_Container.ContainerSize=2then1else0end) >=1andsum(casewhen CCDMS_Container.ContainerSize=3then1else0end) >=1andsum(casewhen CCDMS_Container.ContainerSize=4then1else0end) >=0andsum(casewhen CCDMS_Container.ContainerSize=5then1else0end) >=1

Solution 2:

You can't reference the aliases from your select statement in the having statement. Try rebuilding your query using a subquery to filter it.