Column is invalid error when using derived table

Question

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,
    ROW_NUMBER() OVER(PARTITION BY userId ORDER BY unqid DESC) as sno 
        FROM tblResdagbokenCompetition
) as t
WHERE sno<=2 AND objtypeid=@objTypeID
    AND datAdded BETWEEN @datStart AND @datEnd

Thank you!

Answer 1

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.

Answer 2

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

Answer 3

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