[RESOLVED] IF within select statement
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.
Re: IF within select statement
What database are you using?
Re: IF within select statement
Re: IF within select statement
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.
Re: IF within select statement
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.
Re: IF within select statement
Thanks guys. Someone from another team was able to help.
Re: IF within select statement
Quote:
Originally Posted by
dlary9890
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.