Maybe I'm reading the OP's (clarified) requirements wrong.
But I don't think he wants a count of the number of non-zero values. Rather, he wants the highest prod id with non-zero values.
So maybe MAX instead of COUNT?
select MAX(ProdId) ProdId, mktId
from #temp
where rev <> 0
and qty <>0
group by mktId
order by 1 desc