CTE is behaving unexpectedly when an aggregate function is used











up vote
1
down vote

favorite












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.











share|improve this question
























  • That sum(c) you've got there means you need a group by sn, ed to make the rest of the columns actually group, otherwise it's not clear what you're going for. Maybe you wanted to window the sum as well: sum(c) over (...). Or else you need another subquery.
    – Jeroen Mostert
    Nov 22 at 15:49












  • Yeah! I want to window the sum as well. If I use group by sn, ed then I'm not getting expected results. Row_number won't work in that case.
    – Madhukar
    Nov 22 at 15:59












  • can you explain what results do you expect from this query?
    – IamCP
    Nov 22 at 16:14















up vote
1
down vote

favorite












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.











share|improve this question
























  • That sum(c) you've got there means you need a group by sn, ed to make the rest of the columns actually group, otherwise it's not clear what you're going for. Maybe you wanted to window the sum as well: sum(c) over (...). Or else you need another subquery.
    – Jeroen Mostert
    Nov 22 at 15:49












  • Yeah! I want to window the sum as well. If I use group by sn, ed then I'm not getting expected results. Row_number won't work in that case.
    – Madhukar
    Nov 22 at 15:59












  • can you explain what results do you expect from this query?
    – IamCP
    Nov 22 at 16:14













up vote
1
down vote

favorite









up vote
1
down vote

favorite











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.











share|improve this question















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.








sql sql-server sql-server-2008 sql-server-2012






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 at 15:50









GSerg

58.6k14100217




58.6k14100217










asked Nov 22 at 15:45









Madhukar

705220




705220












  • That sum(c) you've got there means you need a group by sn, ed to make the rest of the columns actually group, otherwise it's not clear what you're going for. Maybe you wanted to window the sum as well: sum(c) over (...). Or else you need another subquery.
    – Jeroen Mostert
    Nov 22 at 15:49












  • Yeah! I want to window the sum as well. If I use group by sn, ed then I'm not getting expected results. Row_number won't work in that case.
    – Madhukar
    Nov 22 at 15:59












  • can you explain what results do you expect from this query?
    – IamCP
    Nov 22 at 16:14


















  • That sum(c) you've got there means you need a group by sn, ed to make the rest of the columns actually group, otherwise it's not clear what you're going for. Maybe you wanted to window the sum as well: sum(c) over (...). Or else you need another subquery.
    – Jeroen Mostert
    Nov 22 at 15:49












  • Yeah! I want to window the sum as well. If I use group by sn, ed then I'm not getting expected results. Row_number won't work in that case.
    – Madhukar
    Nov 22 at 15:59












  • can you explain what results do you expect from this query?
    – IamCP
    Nov 22 at 16:14
















That sum(c) you've got there means you need a group by sn, ed to make the rest of the columns actually group, otherwise it's not clear what you're going for. Maybe you wanted to window the sum as well: sum(c) over (...). Or else you need another subquery.
– Jeroen Mostert
Nov 22 at 15:49






That sum(c) you've got there means you need a group by sn, ed to make the rest of the columns actually group, otherwise it's not clear what you're going for. Maybe you wanted to window the sum as well: sum(c) over (...). Or else you need another subquery.
– Jeroen Mostert
Nov 22 at 15:49














Yeah! I want to window the sum as well. If I use group by sn, ed then I'm not getting expected results. Row_number won't work in that case.
– Madhukar
Nov 22 at 15:59






Yeah! I want to window the sum as well. If I use group by sn, ed then I'm not getting expected results. Row_number won't work in that case.
– Madhukar
Nov 22 at 15:59














can you explain what results do you expect from this query?
– IamCP
Nov 22 at 16:14




can you explain what results do you expect from this query?
– IamCP
Nov 22 at 16:14












1 Answer
1






active

oldest

votes

















up vote
2
down vote



accepted










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





share|improve this answer























  • If I use below query, row_number stays at 1 for each row. I want the top three c for each sn for a particular ed. Example: for sn 'a', there are two ed 1 and 2. So there are two rows with different c. I want the rownumber to display 1 and 2 based on the maximum value of c
    – Madhukar
    Nov 22 at 16:10










  • @Madhukar i have not seen your sample data and output so i just correct your your code and i think you have to change partition by and order by column and i edite query you may check now
    – Zaynul Abadin Tuhin
    Nov 22 at 16:16












  • @Madhukar does it works
    – Zaynul Abadin Tuhin
    Nov 22 at 17:41











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53434391%2fcte-is-behaving-unexpectedly-when-an-aggregate-function-is-used%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes








up vote
2
down vote



accepted










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





share|improve this answer























  • If I use below query, row_number stays at 1 for each row. I want the top three c for each sn for a particular ed. Example: for sn 'a', there are two ed 1 and 2. So there are two rows with different c. I want the rownumber to display 1 and 2 based on the maximum value of c
    – Madhukar
    Nov 22 at 16:10










  • @Madhukar i have not seen your sample data and output so i just correct your your code and i think you have to change partition by and order by column and i edite query you may check now
    – Zaynul Abadin Tuhin
    Nov 22 at 16:16












  • @Madhukar does it works
    – Zaynul Abadin Tuhin
    Nov 22 at 17:41















up vote
2
down vote



accepted










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





share|improve this answer























  • If I use below query, row_number stays at 1 for each row. I want the top three c for each sn for a particular ed. Example: for sn 'a', there are two ed 1 and 2. So there are two rows with different c. I want the rownumber to display 1 and 2 based on the maximum value of c
    – Madhukar
    Nov 22 at 16:10










  • @Madhukar i have not seen your sample data and output so i just correct your your code and i think you have to change partition by and order by column and i edite query you may check now
    – Zaynul Abadin Tuhin
    Nov 22 at 16:16












  • @Madhukar does it works
    – Zaynul Abadin Tuhin
    Nov 22 at 17:41













up vote
2
down vote



accepted







up vote
2
down vote



accepted






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





share|improve this answer














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






share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 22 at 16:17

























answered Nov 22 at 16:05









Zaynul Abadin Tuhin

11k2831




11k2831












  • If I use below query, row_number stays at 1 for each row. I want the top three c for each sn for a particular ed. Example: for sn 'a', there are two ed 1 and 2. So there are two rows with different c. I want the rownumber to display 1 and 2 based on the maximum value of c
    – Madhukar
    Nov 22 at 16:10










  • @Madhukar i have not seen your sample data and output so i just correct your your code and i think you have to change partition by and order by column and i edite query you may check now
    – Zaynul Abadin Tuhin
    Nov 22 at 16:16












  • @Madhukar does it works
    – Zaynul Abadin Tuhin
    Nov 22 at 17:41


















  • If I use below query, row_number stays at 1 for each row. I want the top three c for each sn for a particular ed. Example: for sn 'a', there are two ed 1 and 2. So there are two rows with different c. I want the rownumber to display 1 and 2 based on the maximum value of c
    – Madhukar
    Nov 22 at 16:10










  • @Madhukar i have not seen your sample data and output so i just correct your your code and i think you have to change partition by and order by column and i edite query you may check now
    – Zaynul Abadin Tuhin
    Nov 22 at 16:16












  • @Madhukar does it works
    – Zaynul Abadin Tuhin
    Nov 22 at 17:41
















If I use below query, row_number stays at 1 for each row. I want the top three c for each sn for a particular ed. Example: for sn 'a', there are two ed 1 and 2. So there are two rows with different c. I want the rownumber to display 1 and 2 based on the maximum value of c
– Madhukar
Nov 22 at 16:10




If I use below query, row_number stays at 1 for each row. I want the top three c for each sn for a particular ed. Example: for sn 'a', there are two ed 1 and 2. So there are two rows with different c. I want the rownumber to display 1 and 2 based on the maximum value of c
– Madhukar
Nov 22 at 16:10












@Madhukar i have not seen your sample data and output so i just correct your your code and i think you have to change partition by and order by column and i edite query you may check now
– Zaynul Abadin Tuhin
Nov 22 at 16:16






@Madhukar i have not seen your sample data and output so i just correct your your code and i think you have to change partition by and order by column and i edite query you may check now
– Zaynul Abadin Tuhin
Nov 22 at 16:16














@Madhukar does it works
– Zaynul Abadin Tuhin
Nov 22 at 17:41




@Madhukar does it works
– Zaynul Abadin Tuhin
Nov 22 at 17:41


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53434391%2fcte-is-behaving-unexpectedly-when-an-aggregate-function-is-used%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

What visual should I use to simply compare current year value vs last year in Power BI desktop

How to ignore python UserWarning in pytest?

Alexandru Averescu