Facing issue by using some operation in MsSQL query
i am new to Mssql server i am trying to migrate Mysql queries to Mssql on the process facing issue with this query.
query is:
SELECT converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')),
SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger,
SUM(case when channel='EMAIL' then 1 else 0 end) AS Email,
SUM(case when channel='ECHO' then 1 else 0 end) AS Echo
FROM Conversation
WHERE tenant_id=2 AND createdBy_id in(3)
AND created >= '2018-11-01 06:00:01'
GROUP BY year(created), month(created), DATEPART(week,created),day(created)
ORDER BY created ASC;
And the error which i am facing is:
Column Conversation.created is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
When I am trying to execute to the query without aggregate function its working the combination of converT and sum is not working. Can anybody help me out this.
Thanks in advance
sql-server edited Nov 22 '18 at 7:38
fewlinesofcode
2,0571818
asked Nov 22 '18 at 6:42
learnerlearner
103
add a comment |
i am new to Mssql server i am trying to migrate Mysql queries to Mssql on the process facing issue with this query.
query is:
SELECT converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')),
SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger,
SUM(case when channel='EMAIL' then 1 else 0 end) AS Email,
SUM(case when channel='ECHO' then 1 else 0 end) AS Echo
FROM Conversation
WHERE tenant_id=2 AND createdBy_id in(3)
AND created >= '2018-11-01 06:00:01'
GROUP BY year(created), month(created), DATEPART(week,created),day(created)
ORDER BY created ASC;
And the error which i am facing is:
Column Conversation.created is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
When I am trying to execute to the query without aggregate function its working the combination of converT and sum is not working. Can anybody help me out this.
Thanks in advance
sql-server edited Nov 22 '18 at 7:38
fewlinesofcode
2,0571818
asked Nov 22 '18 at 6:42
learnerlearner
103
rule of thumb, whatever column not in aggregate need to be in theGROUP BYclause. Meaning you need to add thisconverT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York'))to theGROUP BY
– Squirrel
Nov 22 '18 at 6:46
add a comment |
i am new to Mssql server i am trying to migrate Mysql queries to Mssql on the process facing issue with this query.
query is:
SELECT converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')),
SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger,
SUM(case when channel='EMAIL' then 1 else 0 end) AS Email,
SUM(case when channel='ECHO' then 1 else 0 end) AS Echo
FROM Conversation
WHERE tenant_id=2 AND createdBy_id in(3)
AND created >= '2018-11-01 06:00:01'
GROUP BY year(created), month(created), DATEPART(week,created),day(created)
ORDER BY created ASC;
And the error which i am facing is:
Column Conversation.created is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
When I am trying to execute to the query without aggregate function its working the combination of converT and sum is not working. Can anybody help me out this.
Thanks in advance
sql-server edited Nov 22 '18 at 7:38
fewlinesofcode
2,0571818
asked Nov 22 '18 at 6:42
learnerlearner
103
i am new to Mssql server i am trying to migrate Mysql queries to Mssql on the process facing issue with this query.
query is:
SELECT converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')),
SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger,
SUM(case when channel='EMAIL' then 1 else 0 end) AS Email,
SUM(case when channel='ECHO' then 1 else 0 end) AS Echo
FROM Conversation
WHERE tenant_id=2 AND createdBy_id in(3)
AND created >= '2018-11-01 06:00:01'
GROUP BY year(created), month(created), DATEPART(week,created),day(created)
ORDER BY created ASC;
And the error which i am facing is:
Column Conversation.created is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
When I am trying to execute to the query without aggregate function its working the combination of converT and sum is not working. Can anybody help me out this.
Thanks in advance
sql-server sql-server edited Nov 22 '18 at 7:38
fewlinesofcode
2,0571818
asked Nov 22 '18 at 6:42
learnerlearner
103
edited Nov 22 '18 at 7:38
fewlinesofcode
2,0571818
asked Nov 22 '18 at 6:42
learnerlearner
103
edited Nov 22 '18 at 7:38
fewlinesofcode
2,0571818
edited Nov 22 '18 at 7:38
fewlinesofcode
2,0571818
edited Nov 22 '18 at 7:38
fewlinesofcode
2,0571818
2,0571818
asked Nov 22 '18 at 6:42
learnerlearner
103
asked Nov 22 '18 at 6:42
learnerlearner
103
asked Nov 22 '18 at 6:42
learnerlearner
103
103
rule of thumb, whatever column not in aggregate need to be in theGROUP BYclause. Meaning you need to add thisconverT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York'))to theGROUP BY
– Squirrel
Nov 22 '18 at 6:46
add a comment |
rule of thumb, whatever column not in aggregate need to be in theGROUP BYclause. Meaning you need to add thisconverT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York'))to theGROUP BY
– Squirrel
Nov 22 '18 at 6:46
rule of thumb, whatever column not in aggregate need to be in the
GROUP BY clause. Meaning you need to add this converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')) to the GROUP BY– Squirrel
Nov 22 '18 at 6:46
rule of thumb, whatever column not in aggregate need to be in the
GROUP BY clause. Meaning you need to add this converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')) to the GROUP BY– Squirrel
Nov 22 '18 at 6:46
add a comment |
1 Answer
1
active
oldest
votes
It is necessary to add this converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created) in Group By as you already grouped other fields and you want to show this result in Group By:
SELECT
converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')),
SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger,
SUM(case when channel='EMAIL' then 1 else 0 end) AS Email,
SUM(case when channel='ECHO' then 1 else 0 end) AS Echo
FROM Conversation
WHERE tenant_id=2 AND createdBy_id in(3)
AND created >= '2018-11-01 06:00:01'
GROUP BY converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), year(created),
month(created), DATEPART(week,created),day(created)
ORDER BY created ASC;
This error means that you've got SUM of channel field - one row, but SQL Server
has also many rows with created column and it does not know what row should be chosen.
answered Nov 22 '18 at 6:56
StepUpStepUp
7,56474473
Thank you for the response it's working with exclude the order by
– learner
Nov 22 '18 at 7:14
SELECT converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger, SUM(case when channel='EMAIL' then 1 else 0 end) AS Email, SUM(case when channel='ECHO' then 1 else 0 end) AS Echo FROM Conversation WHERE tenant_id=2 AND createdBy_id in(3) AND created >= '2018-11-01 06:00:01' GROUP BY converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), year(created), month(created), DATEPART(week,created),day(created)
– learner
Nov 22 '18 at 7:16
@VrandaRaghuwanshi just addORDER BY createdto the end of your query. And it should work.
– StepUp
Nov 22 '18 at 7:18
add a comment |
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',
autoActivateHeartbeat: false,
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
});
}
});
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%2f53425214%2ffacing-issue-by-using-some-operation-in-mssql-query%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
It is necessary to add this converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created) in Group By as you already grouped other fields and you want to show this result in Group By:
SELECT
converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')),
SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger,
SUM(case when channel='EMAIL' then 1 else 0 end) AS Email,
SUM(case when channel='ECHO' then 1 else 0 end) AS Echo
FROM Conversation
WHERE tenant_id=2 AND createdBy_id in(3)
AND created >= '2018-11-01 06:00:01'
GROUP BY converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), year(created),
month(created), DATEPART(week,created),day(created)
ORDER BY created ASC;
This error means that you've got SUM of channel field - one row, but SQL Server
has also many rows with created column and it does not know what row should be chosen.
answered Nov 22 '18 at 6:56
StepUpStepUp
7,56474473
Thank you for the response it's working with exclude the order by
– learner
Nov 22 '18 at 7:14
SELECT converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger, SUM(case when channel='EMAIL' then 1 else 0 end) AS Email, SUM(case when channel='ECHO' then 1 else 0 end) AS Echo FROM Conversation WHERE tenant_id=2 AND createdBy_id in(3) AND created >= '2018-11-01 06:00:01' GROUP BY converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), year(created), month(created), DATEPART(week,created),day(created)
– learner
Nov 22 '18 at 7:16
@VrandaRaghuwanshi just addORDER BY createdto the end of your query. And it should work.
– StepUp
Nov 22 '18 at 7:18
add a comment |
It is necessary to add this converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created) in Group By as you already grouped other fields and you want to show this result in Group By:
SELECT
converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')),
SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger,
SUM(case when channel='EMAIL' then 1 else 0 end) AS Email,
SUM(case when channel='ECHO' then 1 else 0 end) AS Echo
FROM Conversation
WHERE tenant_id=2 AND createdBy_id in(3)
AND created >= '2018-11-01 06:00:01'
GROUP BY converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), year(created),
month(created), DATEPART(week,created),day(created)
ORDER BY created ASC;
This error means that you've got SUM of channel field - one row, but SQL Server
has also many rows with created column and it does not know what row should be chosen.
answered Nov 22 '18 at 6:56
StepUpStepUp
7,56474473
Thank you for the response it's working with exclude the order by
– learner
Nov 22 '18 at 7:14
SELECT converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger, SUM(case when channel='EMAIL' then 1 else 0 end) AS Email, SUM(case when channel='ECHO' then 1 else 0 end) AS Echo FROM Conversation WHERE tenant_id=2 AND createdBy_id in(3) AND created >= '2018-11-01 06:00:01' GROUP BY converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), year(created), month(created), DATEPART(week,created),day(created)
– learner
Nov 22 '18 at 7:16
@VrandaRaghuwanshi just addORDER BY createdto the end of your query. And it should work.
– StepUp
Nov 22 '18 at 7:18
add a comment |
It is necessary to add this converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created) in Group By as you already grouped other fields and you want to show this result in Group By:
SELECT
converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')),
SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger,
SUM(case when channel='EMAIL' then 1 else 0 end) AS Email,
SUM(case when channel='ECHO' then 1 else 0 end) AS Echo
FROM Conversation
WHERE tenant_id=2 AND createdBy_id in(3)
AND created >= '2018-11-01 06:00:01'
GROUP BY converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), year(created),
month(created), DATEPART(week,created),day(created)
ORDER BY created ASC;
This error means that you've got SUM of channel field - one row, but SQL Server
has also many rows with created column and it does not know what row should be chosen.
answered Nov 22 '18 at 6:56
StepUpStepUp
7,56474473
It is necessary to add this converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created) in Group By as you already grouped other fields and you want to show this result in Group By:
SELECT
converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')),
SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger,
SUM(case when channel='EMAIL' then 1 else 0 end) AS Email,
SUM(case when channel='ECHO' then 1 else 0 end) AS Echo
FROM Conversation
WHERE tenant_id=2 AND createdBy_id in(3)
AND created >= '2018-11-01 06:00:01'
GROUP BY converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), year(created),
month(created), DATEPART(week,created),day(created)
ORDER BY created ASC;
This error means that you've got SUM of channel field - one row, but SQL Server
has also many rows with created column and it does not know what row should be chosen.
answered Nov 22 '18 at 6:56
StepUpStepUp
7,56474473
edited Nov 22 '18 at 7:05
answered Nov 22 '18 at 6:56
StepUpStepUp
7,56474473
answered Nov 22 '18 at 6:56
StepUpStepUp
7,56474473
answered Nov 22 '18 at 6:56
StepUpStepUp
7,56474473
7,56474473
Thank you for the response it's working with exclude the order by
– learner
Nov 22 '18 at 7:14
SELECT converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger, SUM(case when channel='EMAIL' then 1 else 0 end) AS Email, SUM(case when channel='ECHO' then 1 else 0 end) AS Echo FROM Conversation WHERE tenant_id=2 AND createdBy_id in(3) AND created >= '2018-11-01 06:00:01' GROUP BY converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), year(created), month(created), DATEPART(week,created),day(created)
– learner
Nov 22 '18 at 7:16
@VrandaRaghuwanshi just addORDER BY createdto the end of your query. And it should work.
– StepUp
Nov 22 '18 at 7:18
add a comment |
Thank you for the response it's working with exclude the order by
– learner
Nov 22 '18 at 7:14
SELECT converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger, SUM(case when channel='EMAIL' then 1 else 0 end) AS Email, SUM(case when channel='ECHO' then 1 else 0 end) AS Echo FROM Conversation WHERE tenant_id=2 AND createdBy_id in(3) AND created >= '2018-11-01 06:00:01' GROUP BY converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), year(created), month(created), DATEPART(week,created),day(created)
– learner
Nov 22 '18 at 7:16
@VrandaRaghuwanshi just addORDER BY createdto the end of your query. And it should work.
– StepUp
Nov 22 '18 at 7:18
Thank you for the response it's working with exclude the order by
– learner
Nov 22 '18 at 7:14
Thank you for the response it's working with exclude the order by
– learner
Nov 22 '18 at 7:14
SELECT converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger, SUM(case when channel='EMAIL' then 1 else 0 end) AS Email, SUM(case when channel='ECHO' then 1 else 0 end) AS Echo FROM Conversation WHERE tenant_id=2 AND createdBy_id in(3) AND created >= '2018-11-01 06:00:01' GROUP BY converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), year(created), month(created), DATEPART(week,created),day(created)
– learner
Nov 22 '18 at 7:16
SELECT converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), SUM(case when channel='FACEBOOK' then 1 else 0 end) AS Messenger, SUM(case when channel='EMAIL' then 1 else 0 end) AS Email, SUM(case when channel='ECHO' then 1 else 0 end) AS Echo FROM Conversation WHERE tenant_id=2 AND createdBy_id in(3) AND created >= '2018-11-01 06:00:01' GROUP BY converT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York')), year(created), month(created), DATEPART(week,created),day(created)
– learner
Nov 22 '18 at 7:16
@VrandaRaghuwanshi just add
ORDER BY created to the end of your query. And it should work.– StepUp
Nov 22 '18 at 7:18
@VrandaRaghuwanshi just add
ORDER BY created to the end of your query. And it should work.– StepUp
Nov 22 '18 at 7:18
add a comment |
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.
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%2f53425214%2ffacing-issue-by-using-some-operation-in-mssql-query%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
rule of thumb, whatever column not in aggregate need to be in the
GROUP BYclause. Meaning you need to add thisconverT(datetime,SWITCHOFFSET(converT(datetimeoffset,created), 'America/New_York'))to theGROUP BY– Squirrel
Nov 22 '18 at 6:46