0
SELECT 
    CCDMS_Company.CompanyID, CCDMS_Company.CompanyName,
    CCDMS_Container.ContainerID, CCDMS_Container.Price, 
    SUM(CASE WHEN CCDMS_Container.ContainerSize = 1
             THEN 1 ELSE 0 END) AS size1,
    SUM(CASE WHEN CCDMS_Container.ContainerSize = 2
             THEN 1 ELSE 0 END) AS size2,
    SUM(CASE WHEN CCDMS_Container.ContainerSize = 3
             THEN 1 ELSE 0 END) AS size3,
    SUM(CASE WHEN CCDMS_Container.ContainerSize = 4
             THEN 1 ELSE 0 END) AS size4,
    SUM(CASE WHEN CCDMS_Container.ContainerSize = 5
             THEN 1 ELSE 0 END) AS size5
FROM 
    CCDMS_Company
INNER JOIN 
    CCDMS_Container ON CCDMS_Container.Co_ID = CCDMS_Company.CompanyID
                    AND CCDMS_Company.CityID = 1
GROUP BY 
    CCDMS_Company.CompanyID, CCDMS_Company.CompanyName,
    CCDMS_Container.ContainerID, CCDMS_Container.Price
HAVING
    size1 >=2
    AND size2 >= 1
    AND size3 >= 1
    AND size4 >= 0
    AND size5 >= 1 ;

This is my query and I keep getting this error even when I use where .

I don't know what to do .

Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'having'.

chancrovsky
  • 582
  • 3
  • 11
Nysa
  • 425
  • 1
  • 5
  • 14
  • Put calculations into CTE / subquery, then filter with `where` referencing aliases `size*`. – Ivan Starostin Mar 08 '16 at 21:31
  • I can appreciate that the `having` clause is syntactically incorrect because it doesn't understand the column aliases. However, that should generate an "Invalid column name" error rather than an "Incorrect syntax" error. I suspect that you have an invalid character somewhere around the `having` keyword. – Gordon Linoff Mar 08 '16 at 22:17
  • I'm trying to do something similar to this http://stackoverflow.com/questions/17194145/sql-count-based-on-column-value but what I want to do after that is to return only the companies that have the same or more of the specified numbers I saw also this question http://stackoverflow.com/questions/15618812/sum-columns-in-mysql-then-use-the-result-in-where-clause so I combined both of them hopping that it will work with me. – Nysa Mar 08 '16 at 22:30
  • How can I do it with subquery I tried but the result was not correct. – Nysa Mar 08 '16 at 22:34

2 Answers2

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(case when CCDMS_Container.ContainerSize=1
 then 1 else 0 end)as size1,
sum(case when CCDMS_Container.ContainerSize=2
 then 1 else 0 end)as size2,
sum(case when CCDMS_Container.ContainerSize=3
 then 1 else 0 end)as size3,
sum(case when CCDMS_Container.ContainerSize=4
 then 1 else 0 end)as size4,
sum(case when CCDMS_Container.ContainerSize=5
 then 1 else 0 end)as size5
   from 
CCDMS_Company
 inner join
CCDMS_Container
on CCDMS_Container.Co_ID = CCDMS_Company.CompanyID
and 
CCDMS_Company.CityID = 1
group by 
CCDMS_Company.CompanyID,CCDMS_Company.CompanyName,CCDMS_Container.ContainerID,CCDMS_Container.Price

having
    sum(case when CCDMS_Container.ContainerSize=1 then 1 else 0 end) >=2
and sum(case when CCDMS_Container.ContainerSize=2 then 1 else 0 end) >= 1
and sum(case when CCDMS_Container.ContainerSize=3 then 1 else 0 end) >= 1
and sum(case when CCDMS_Container.ContainerSize=4 then 1 else 0 end) >= 0
and sum(case when CCDMS_Container.ContainerSize=5 then 1 else 0 end) >= 1
FLICKER
  • 6,439
  • 4
  • 45
  • 75
  • It's working but unfortunately it's not returning the right result. – Nysa Mar 08 '16 at 22:32
  • @Nysa, what you get and what is expected? – FLICKER Mar 08 '16 at 22:40
  • I have 3 companies 1001,1002 ,1003 and 11 containers with different sizes 1,2,3,4, 5 I want return only the containers that are in the companies that have the same amount or more of specified numbers. for example if I want 2 containers from size 1 and 3 containers from size 2 then only the containers in the company that has 2 or more of size 1 and 3 or more of size 2 should appear let's say that only company 1001 has them then it should appear alone. but know it returns all of them. – Nysa Mar 08 '16 at 23:05
  • @Nysa, Please ask a new question about it. – FLICKER Mar 08 '16 at 23:11
0

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

EduardoCMB
  • 392
  • 2
  • 17