SQL COUNT specific unit IF another field <> 0

I have a query that has a select statement that contains the foll开发者_StackOverflowowing:

,COUNT(u.[Unit])
,up.[Number_Of_Stops]

I need to only count the units where number of stops <> 0. This has more details in the query so I can't just say WHERE number_of_stops <> 0. It has to be within the select statement.

Thanks

Try:

SUM(CASE WHEN up.[Number_Of_Stops] != 0 THEN 1 ELSE 0 END) AS countWhereNumStopsNotZero

(Edit: original answer said "COUNT" not "SUM")