Subquery result in the WHERE Clause

I’d like to run a sql like this

SELECT *,
       (SELECT COUNT(*)FROM ExpandQa WHERE ExpandQa.questionId = id) AS qaCount
FROM Question
WHERE (actId = 9)
  AND (flowState = 40)
  AND (((type = 0
     AND qaCount BETWEEN 1 AND 29)
     OR (type <> 0
     AND qaCount BETWEEN 1 AND 19)))
ORDER BY id DESC;

The result is –> invalid column name ‘qaCount’。
My db is SQL Server.

I can fix the problem like this

SELECT *,
       (SELECT COUNT(*)FROM ExpandQa WHERE ExpandQa.questionId = id) AS qaCount
FROM Question
WHERE (actId = 9)
  AND (flowState = 40)
  AND (((type = 0
     AND (SELECT COUNT(*)FROM ExpandQa WHERE ExpandQa.questionId = id) BETWEEN 1 AND 29)
     OR (type <> 0
     AND (SELECT COUNT(*)FROM ExpandQa WHERE ExpandQa.questionId = id) BETWEEN 1 AND 19)))
ORDER BY id DESC;

But I’m afraid that it will be slower.
"qaCount" is used three times. I don’t hope "(SELECT COUNT(*) FROM ExpandQa WHERE ExpandQa.questionId=id)" is written three times.

Is there a good way to do that? Thanks.

Thanks all.
Finally, I use the SQL and it’s correct now.

WITH T AS (
  SELECT *, (SELECT COUNT(*) FROM ExpandQa WHERE ExpandQa.questionId=id) as qaCount
  FROM   Question
) SELECT *
FROM T
WHERE  actId = 9
  AND flowState = 40
  AND (   (type =  0 AND qaCount BETWEEN 1 AND 29)
       OR (type <> 0 AND qaCount BETWEEN 1 AND 19))
ORDER BY id DESC;

"With" is a good keyword!

>Solution :

Simplify as :

WITH
T AS
(
SELECT *, COUNT(ExpandQa.questionId) OVER(PARTITION BY id) AS qaCount
FROM   Question
)
SELECT *
FROM   T
WHERE  actId = 9
  AND flowState = 40
  AND (   (type =  0 AND qaCount BETWEEN 1 AND 29)
       OR (type <> 0 AND qaCount BETWEEN 1 AND 19))
ORDER BY id DESC;

Also, this predicate

AND (   (type =  0 AND qaCount BETWEEN 1 AND 29)
       OR (type <> 0 AND qaCount BETWEEN 1 AND 19))

can be optimized with :

AND qaCount BETWEEN 1 AND CASE type WHEN 0 THEN 29 ELSE 19 END