CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Madhukar
Madhukar

Reputation: 1242

CTE is behaving unexpectedly when an aggregate function is used

When I'm using an aggregate function inside CTE, I'm getting an error message. Below is the query and the error message associated with it. It is working fine when I remove the aggregate function. Somewhere I'm missing the trick.

Code: 

;
With CTE as (
    Select
        sn,
        ed,
        sum(c) c,
        row_number() over (partition by sn, ed order by sn, sum(c) desc, ed) rn
    from TD
    where ed >= '11/15/2018'
)
select * from CTE

Error message:

Column TD.sn is invalid in the select list because it is not contained in either an aggregate function or the group by clause.

Upvotes: 1

Views: 415

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

You have used aggregate function but not used group by in your query

With CTE as (
    Select
        sn,
        ed,
        sum(c) c,
        row_number() over (partition by sn order by  sum(c) desc) rn
    from TD
    where ed >= '11/15/2018'
   group by sn,
        ed
)
select * from CTE where rn<=3

Upvotes: 2

Related Questions