#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    South East of England
    Posts
    11
    Rep Power
    0

    Question 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?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    South East of England
    Posts
    11
    Rep Power
    0
    Thank you r937, you were right, coalesce was better for what I wanted.

IMN logo majestic logo threadwatch logo seochat tools logo