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.snis 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 edited Nov 22 at 15:50
GSerg
58.6k14100217
asked Nov 22 at 15:45
Madhukar
705220
add a comment |
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.snis 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 edited Nov 22 at 15:50
GSerg
58.6k14100217
asked Nov 22 at 15:45
Madhukar
705220
Thatsum(c)you've got there means you need agroup by sn, edto make the rest of the columns actually group, otherwise it's not clear what you're going for. Maybe you wanted to window thesumas 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
add a comment |
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.snis 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 edited Nov 22 at 15:50
GSerg
58.6k14100217
asked Nov 22 at 15:45
Madhukar
705220
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.snis 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 sql
sql-server sql-server-2008 sql-server-2012 edited Nov 22 at 15:50
GSerg
58.6k14100217
asked Nov 22 at 15:45
Madhukar
705220
edited Nov 22 at 15:50
GSerg
58.6k14100217
asked Nov 22 at 15:45
Madhukar
705220
edited Nov 22 at 15:50
GSerg
58.6k14100217
edited Nov 22 at 15:50
GSerg
58.6k14100217
edited Nov 22 at 15:50
GSerg
58.6k14100217
58.6k14100217
asked Nov 22 at 15:45
Madhukar
705220
asked Nov 22 at 15:45
Madhukar
705220
asked Nov 22 at 15:45
Madhukar
705220
705220
Thatsum(c)you've got there means you need agroup by sn, edto make the rest of the columns actually group, otherwise it's not clear what you're going for. Maybe you wanted to window thesumas 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
add a comment |
Thatsum(c)you've got there means you need agroup by sn, edto make the rest of the columns actually group, otherwise it's not clear what you're going for. Maybe you wanted to window thesumas 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
add a comment |
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
answered Nov 22 at 16:05
Zaynul Abadin Tuhin
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
add a comment |
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
answered Nov 22 at 16:05
Zaynul Abadin Tuhin
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
add a comment |
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
answered Nov 22 at 16:05
Zaynul Abadin Tuhin
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
add a comment |
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
answered Nov 22 at 16:05
Zaynul Abadin Tuhin
11k2831
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
answered Nov 22 at 16:05
Zaynul Abadin Tuhin
11k2831
edited Nov 22 at 16:17
answered Nov 22 at 16:05
Zaynul Abadin Tuhin
11k2831
answered Nov 22 at 16:05
Zaynul Abadin Tuhin
11k2831
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
add a comment |
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
add a comment |
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
That
sum(c)you've got there means you need agroup by sn, edto make the rest of the columns actually group, otherwise it's not clear what you're going for. Maybe you wanted to window thesumas 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