[RESOLVED] IF within select statement

[dlary9890]

I have a SQL select statement that calculates the value of one of the elements being returned by the select. Following is that SQL.
, AVGManual =
(select count(*)
from CustomerFeedback as AV
where av.SellingArea <> 'Find Merchandise'
and av.Store = cf.Store
)
/ (select count(distinct(tl.associatenumber))
from CustomerFeedback as tl
where tl.SellingArea <> 'Find Merchandise'
and tl.Store = cf.Store
)

----------------------------------------------------------------------------------
The problem is that the divisor is a zero in some cases and gets an error.
I tried writing an IF statement but it is giving me all kinds of errors. See
below:
, if (select count(distinct(tl.associatenumber))
from CustomerFeedback as tl
where tl.SellingArea <> 'Find Merchandise'
and tl.Store = cf.Store) > 0
BEGIN
AVGManual =
(select count(*)
from CustomerFeedback as AV
where av.SellingArea <> 'F M'
and av.Store = cf.Store
)
/ (select count(distinct(tl.associatenumber))
from CustomerFeedback as tl
where tl.SellingArea <> 'F M'
and tl.Store = cf.Store
)
END
ELSE
AVGManual = 0

, PCTTTL = 22

------------------------------------------------------------------------------------
I want AVGManual to be 0 or a calculated value.

[abhijit]

What database are you using?

[dlary9890]

SQL Server 2008 R2

[abhijit]

Could you please post the table structure and a few insert rows for your data?

I think this doesn't require an IF condition at all.

It is possible to use the AVG function in SQL Server and the COALESCE function to get you the results you need. That would also generate the results faster compared to the way you're currently going about it.

[FunkyDexter]

I agree with abhijit that there are probably easier ways of doing this but there are a few obvious problems with what you've done so far. First of all you can't just assign a value to a variable like that in TSQL. You need to SET it:-
Set @Var = (Select Value From Somewhere)

or Select it:-
Select @Var = Value From Somewhere

Other than that you'd really have to post your full SQL (I can se you've started with a comma so I don't think we're seeing everything) and tell us what the errors actually are.

[dlary9890]

Thanks guys. Someone from another team was able to help.

[abhijit]

Thanks guys. Someone from another team was able to help.

In this case, you should post the solution that you used and mark this thread as resolved.