Post reply

count

  • September 20, 2013 at 12:14 pm

    #280009

    I need to check each Prodmonth ( There are 12 in all) and if the count for particular month is > 1 that means there are multiple entries for that month .

    Query :

    select COUNT(prodmonth),* from dbo.Proddata

    having COUNT(prodmonth) >1

    order by ProdMonth

    Error : invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Kindly advise.

  • September 20, 2013 at 12:34 pm

    #1652010

    When using aggregates (COUNT,SUM,AVG, etc.), you need to use a GROUP BY statement. In this case, it seems you need Prodmonth, and a count of the number of times Prodmonth is in your table; is this correct? If so, change your query slightly to:

    select ProdMonth,COUNT(prodmonth)

    from dbo.Proddata

    GROUP BY ProdMonth

    having COUNT(prodmonth) >1

    order by ProdMonth

    If you need the rest of the data along with the months, this query can be placed into temp storage (CTE, temp table, subquery) and JOINed to the original table.

    Basically, GROUP BY is needed because you have to have something to run the aggregate against; in this case, it's the number of times ProdMonth appears. Further reading on the subject is at the link below:

    Group By

    - 😀

  • September 20, 2013 at 1:24 pm

    #1652027

    Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply