Subquery return more than one value error when calling subquery inside case statement

Question

I'm working with a very long tsql query(Query1). I want to replace a query(query2) which is inside this Query1 to improve performance of the Query1. The query that is being replaced by query2 is query3.

....[column]/Case when (query2) <> 0 then (query2) 
    else 1 end) * ....

query2 returns a column. The query i built to replace query2 which is query3 is also returning the same column. But when i replace query3 with query2 i get the following error.

'Subquery returned more than one value. This is not permitted when the subquery follows =,!=, ,>= or when the subquery is used as an expression'

query2:

select sum(column1) 
    from [Table1]
    Where ....
    group by .....

I created a table with an inner join and used a select statement to get data as query 3.

query4: with tableabc as (query)

query3: select [theonlycolumn_in tableabc] from tableabc

Answer 1

Your query2 probably returns scalar value if your where clause filters out the column on which you're grouping by to single value.

If you want to exclude results where query3 returns at least one 0 value, you can do smth like this:

case when 0 not in (query3) then (query2) ...

But in your query you can't replace ...then (query2) with ..then (query3) because you can't divide column by another column returned from result set without joining them, because how would sql server know by which value from second column should first column be divided. You must have a join condition for such actions.