Column is invalid error when using derived table

I'm using ROW_NUMBER() and a derived table to fetch data from the derived table result.

However, I get the error message telling me I don't have the appropriate columns in the GROUP BY clause.

Here's the error:

Column 'tblCompetition.objID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

What column am I missing? Or am I doing something else wrong? Find below the query that is not working, and the (more simple) query that is working.

SQL Server 2008.

Query that isn't working:

SELECT
    objID,
    objTypeID,
    userID,
    datAdded,
    count,
    sno
FROM
(
    SELECT scc.objID,scc.objTypeID,scc.userID,scc.datAdded,
       COUNT(sci.favID) as count,
    ROW_NUMBER() OVER(PARTITION BY scc.userID ORDER BY scc.unqID DESC) as sno 
    FROM tblCompetition scc
    LEFT JOIN tblFavourites sci
    ON sci.favID = scc.objID
        AND sci.datTimeStamp BETWEEN @datStart AND @datEnd
) as t
WHERE sno <= 2 AND objTypeID = @objTypeID
   AND datAdded BETWEEN @datStart AND @datEnd
GROUP BY objID,objTypeID,userID,datAdded,count,sno

Simple query that is working:

SELECT objId,objTypeID,userId,datAdded FROM
(
        SELECT objId,objTypeID,userId,datAdded,
 开发者_如何学Go   ROW_NUMBER() OVER(PARTITION BY userId ORDER BY unqid DESC) as sno 
        FROM tblRdbCompetition
) as t
WHERE sno<=2 AND objtypeid=@objTypeID
    AND datAdded BETWEEN @datStart AND @datEnd

Thank you!

you need the GROUP BY in your subquery since that's where the aggregate is:

SELECT
objID,
objTypeID,
userID,
datAdded,
count,
sno
FROM
(
SELECT scc.objID,scc.objTypeID,scc.userID,scc.datAdded,
   COUNT(sci.favID) as count,
ROW_NUMBER() OVER(PARTITION BY scc.userID ORDER BY scc.unqID DESC) as sno 
FROM tblCompetition scc
LEFT JOIN tblFavourites sci
ON sci.favID = scc.objID
    AND sci.datTimeStamp BETWEEN @datStart AND @datEnd
GROUP BY scc.objID,scc.objTypeID,scc.userID,scc.datAdded) as t
WHERE sno <= 2 AND objTypeID = @objTypeID
AND datAdded BETWEEN @datStart AND @datEnd

You cannot have count in a group by clause. Infact the count is derived when you have other fields in group by. Remove count from your Group by.

In the innermost query you are using

COUNT(sci.favID) as count,

which is an aggregate, and you select other non-aggregating columns along with it.

I believe you wanted an analytic COUNT instead:

SELECT  objID,
        objTypeID,
        userID,
        datAdded,
        count,
        sno
FROM    (
        SELECT  scc.objID,scc.objTypeID,scc.userID,scc.datAdded,
                COUNT(sci.favID) OVER (PARTITION BY scc.userID ) AS count,
                ROW_NUMBER() OVER (PARTITION BY scc.userID ORDER BY scc.unqID DESC) as sno 
        FROM    tblCompetition scc
        LEFT JOIN
                tblFavourites sci
        ON      sci.favID = scc.objID
                AND sci.datTimeStamp BETWEEN @datStart AND @datEnd
        ) as t
WHERE   sno  = 1
        AND objTypeID = @objTypeID