Problem using count function

Hi,

I am having a problem trying to use COUNT in the following statement in a Stored Procedure;

select
#TEMPTABLE.ID,
#TEMPTABLE.DESC,
#TEMPTABLE.PDF,
#TEMPTABLE.WEB,
#TEMPTABLE.POST,
#TEMPTABLE.COMMENT,
#TEMPTABLE.LINK,
count(#TEMPTABLE.WEB) as NUMWEB,
from #TEMPTABLE
order by #TEMPTABLE.ID

which returns the error;

Column '#TEMPTABLE.ID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause

the value WEB should be either 1 or null and I want NUMWEB to indicate whether their are any set to 1 or not.

Any ideas?

any ideas? yup

add a GROUP BY clause, and don't select or group by the thing you're counting

select
#TEMPTABLE.ID,
#TEMPTABLE.DESC,
#TEMPTABLE.PDF,
#TEMPTABLE.POST,
#TEMPTABLE.COMMENT,
#TEMPTABLE.LINK,
count(#TEMPTABLE.WEB) as NUMWEB,
from #TEMPTABLE
GROUP BY
#TEMPTABLE.ID,
#TEMPTABLE.DESC,
#TEMPTABLE.PDF,
#TEMPTABLE.POST,
#TEMPTABLE.COMMENT,
#TEMPTABLE.LINK
order by #TEMPTABLE.ID

okay, that's it from a syntactic point of view -- it should at least run, i.e. not give an error message

however, it may not be what you want from a semantic point of view, because ID is probably unique, and you will likely get only one row per group, so that NUMWEB will be either 1 or 0, depending on whether WEB was 1 or null

if that's the case, then you might as well not bother grouping or counting --

select
#TEMPTABLE.ID,
#TEMPTABLE.DESC,
#TEMPTABLE.PDF,
#TEMPTABLE.POST,
#TEMPTABLE.COMMENT,
#TEMPTABLE.LINK,
coalesce(#TEMPTABLE.WEB,0) as NUMWEB -- 1 or 0
from #TEMPTABLE
order by #TEMPTABLE.ID

Thank you r937, you were right, coalesce was better for what I wanted.