Aggregate function error while using group by clause in SQL
I have table named purchase. It has columns billno, billdate, qty, amount. When I run group by query, it is throwing an error.
Query I used
SELECT
BILLNO,
BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM
PURCHASE
GROUP BY
BILLNO
This is the error I'm getting - how to get bill wise total amount?
Column 'PURCHASE.BILLDATE' 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 edited Dec 30 '18 at 7:53
marc_s
574k12811091256
asked Dec 30 '18 at 5:05
Hari Ram StrHari Ram Str
102
add a comment |
I have table named purchase. It has columns billno, billdate, qty, amount. When I run group by query, it is throwing an error.
Query I used
SELECT
BILLNO,
BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM
PURCHASE
GROUP BY
BILLNO
This is the error I'm getting - how to get bill wise total amount?
Column 'PURCHASE.BILLDATE' 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 edited Dec 30 '18 at 7:53
marc_s
574k12811091256
asked Dec 30 '18 at 5:05
Hari Ram StrHari Ram Str
102
3
billdatecolumn has to either be in aggregated (sum,max, etc.) or included in thegroup byclause.
– tarheel
Dec 30 '18 at 5:25
If I include the billdate column in group by I'm not getting the desired result @tarheel
– Hari Ram Str
Dec 30 '18 at 5:55
2
Then you'll need to aggregate it. For more specific help, please provide sample data and expected output.
– tarheel
Dec 30 '18 at 6:03
add a comment |
I have table named purchase. It has columns billno, billdate, qty, amount. When I run group by query, it is throwing an error.
Query I used
SELECT
BILLNO,
BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM
PURCHASE
GROUP BY
BILLNO
This is the error I'm getting - how to get bill wise total amount?
Column 'PURCHASE.BILLDATE' 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 edited Dec 30 '18 at 7:53
marc_s
574k12811091256
asked Dec 30 '18 at 5:05
Hari Ram StrHari Ram Str
102
I have table named purchase. It has columns billno, billdate, qty, amount. When I run group by query, it is throwing an error.
Query I used
SELECT
BILLNO,
BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM
PURCHASE
GROUP BY
BILLNO
This is the error I'm getting - how to get bill wise total amount?
Column 'PURCHASE.BILLDATE' 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
sql-server sql-server-2008 edited Dec 30 '18 at 7:53
marc_s
574k12811091256
asked Dec 30 '18 at 5:05
Hari Ram StrHari Ram Str
102
edited Dec 30 '18 at 7:53
marc_s
574k12811091256
asked Dec 30 '18 at 5:05
Hari Ram StrHari Ram Str
102
edited Dec 30 '18 at 7:53
marc_s
574k12811091256
edited Dec 30 '18 at 7:53
marc_s
574k12811091256
edited Dec 30 '18 at 7:53
marc_s
574k12811091256
574k12811091256
asked Dec 30 '18 at 5:05
Hari Ram StrHari Ram Str
102
asked Dec 30 '18 at 5:05
Hari Ram StrHari Ram Str
102
asked Dec 30 '18 at 5:05
Hari Ram StrHari Ram Str
102
102
3
billdatecolumn has to either be in aggregated (sum,max, etc.) or included in thegroup byclause.
– tarheel
Dec 30 '18 at 5:25
If I include the billdate column in group by I'm not getting the desired result @tarheel
– Hari Ram Str
Dec 30 '18 at 5:55
2
Then you'll need to aggregate it. For more specific help, please provide sample data and expected output.
– tarheel
Dec 30 '18 at 6:03
add a comment |
3
billdatecolumn has to either be in aggregated (sum,max, etc.) or included in thegroup byclause.
– tarheel
Dec 30 '18 at 5:25
If I include the billdate column in group by I'm not getting the desired result @tarheel
– Hari Ram Str
Dec 30 '18 at 5:55
2
Then you'll need to aggregate it. For more specific help, please provide sample data and expected output.
– tarheel
Dec 30 '18 at 6:03
3
3
billdate column has to either be in aggregated (sum, max, etc.) or included in the group by clause.– tarheel
Dec 30 '18 at 5:25
billdate column has to either be in aggregated (sum, max, etc.) or included in the group by clause.– tarheel
Dec 30 '18 at 5:25
If I include the billdate column in group by I'm not getting the desired result @tarheel
– Hari Ram Str
Dec 30 '18 at 5:55
If I include the billdate column in group by I'm not getting the desired result @tarheel
– Hari Ram Str
Dec 30 '18 at 5:55
2
2
Then you'll need to aggregate it. For more specific help, please provide sample data and expected output.
– tarheel
Dec 30 '18 at 6:03
Then you'll need to aggregate it. For more specific help, please provide sample data and expected output.
– tarheel
Dec 30 '18 at 6:03
add a comment |
3 Answers
3
active
oldest
votes
The error is pretty obvious. The unaggregated columns in the SELECT of an aggregation query need to match the keys. In your query, BILLDATE is not aggregated and it is not a key.
The simple fix is:
SELECT BILLNO, BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM PURCHASE
GROUP BY BILLNO, BILLDATE;
If you want only one row per BILLNO -- or if you know that BILLDATE is the same for all BILLNO -- then you can use an aggregation function instead:
SELECT BILLNO, MAX(BILLDATE) as BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM PURCHASE
GROUP BY BILLNO;
answered Dec 30 '18 at 12:49
Gordon LinoffGordon Linoff
767k35300402
add a comment |
According to the official documentation，‘The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list. However, each table or view column in any nonaggregate expression in the list must be included in the GROUP BY list’， it will indicate the cause of your error.
For more details , you can refer to this link and you will see some examples about your issue: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017#arguments
There is a great example to explain for you : https://www.codeproject.com/Articles/1110163/%2FArticles%2F1110163%2FSQL-GROUP-By-and-the-Column-name-is-invalid-in-the
answered Jan 2 at 8:36
Rachel_WangRachel_Wang
243
add a comment |
Here we have to revise the Concept of using the Group By and Order By in A SQL Query.
Remember One thing the name of column that has been came in Order By cannot be used inside aggregate function such as SUM, Average or MAX.
So don't put Same Column Name in Aggregate Function and with Order By too.
answered Jan 13 at 14:35
Abdur Rehman KhalidAbdur Rehman Khalid
315
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%2f53975404%2faggregate-function-error-while-using-group-by-clause-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
The error is pretty obvious. The unaggregated columns in the SELECT of an aggregation query need to match the keys. In your query, BILLDATE is not aggregated and it is not a key.
The simple fix is:
SELECT BILLNO, BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM PURCHASE
GROUP BY BILLNO, BILLDATE;
If you want only one row per BILLNO -- or if you know that BILLDATE is the same for all BILLNO -- then you can use an aggregation function instead:
SELECT BILLNO, MAX(BILLDATE) as BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM PURCHASE
GROUP BY BILLNO;
answered Dec 30 '18 at 12:49
Gordon LinoffGordon Linoff
767k35300402
add a comment |
The error is pretty obvious. The unaggregated columns in the SELECT of an aggregation query need to match the keys. In your query, BILLDATE is not aggregated and it is not a key.
The simple fix is:
SELECT BILLNO, BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM PURCHASE
GROUP BY BILLNO, BILLDATE;
If you want only one row per BILLNO -- or if you know that BILLDATE is the same for all BILLNO -- then you can use an aggregation function instead:
SELECT BILLNO, MAX(BILLDATE) as BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM PURCHASE
GROUP BY BILLNO;
answered Dec 30 '18 at 12:49
Gordon LinoffGordon Linoff
767k35300402
add a comment |
The error is pretty obvious. The unaggregated columns in the SELECT of an aggregation query need to match the keys. In your query, BILLDATE is not aggregated and it is not a key.
The simple fix is:
SELECT BILLNO, BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM PURCHASE
GROUP BY BILLNO, BILLDATE;
If you want only one row per BILLNO -- or if you know that BILLDATE is the same for all BILLNO -- then you can use an aggregation function instead:
SELECT BILLNO, MAX(BILLDATE) as BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM PURCHASE
GROUP BY BILLNO;
answered Dec 30 '18 at 12:49
Gordon LinoffGordon Linoff
767k35300402
The error is pretty obvious. The unaggregated columns in the SELECT of an aggregation query need to match the keys. In your query, BILLDATE is not aggregated and it is not a key.
The simple fix is:
SELECT BILLNO, BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM PURCHASE
GROUP BY BILLNO, BILLDATE;
If you want only one row per BILLNO -- or if you know that BILLDATE is the same for all BILLNO -- then you can use an aggregation function instead:
SELECT BILLNO, MAX(BILLDATE) as BILLDATE,
SUM(QTY) AS SUMQTY,
SUM(AMOUNT) AS SUMAMOUNT
FROM PURCHASE
GROUP BY BILLNO;
answered Dec 30 '18 at 12:49
Gordon LinoffGordon Linoff
767k35300402
answered Dec 30 '18 at 12:49
Gordon LinoffGordon Linoff
767k35300402
answered Dec 30 '18 at 12:49
Gordon LinoffGordon Linoff
767k35300402
answered Dec 30 '18 at 12:49
Gordon LinoffGordon Linoff
767k35300402
767k35300402
add a comment |
add a comment |
According to the official documentation，‘The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list. However, each table or view column in any nonaggregate expression in the list must be included in the GROUP BY list’， it will indicate the cause of your error.
For more details , you can refer to this link and you will see some examples about your issue: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017#arguments
There is a great example to explain for you : https://www.codeproject.com/Articles/1110163/%2FArticles%2F1110163%2FSQL-GROUP-By-and-the-Column-name-is-invalid-in-the
answered Jan 2 at 8:36
Rachel_WangRachel_Wang
243
add a comment |
According to the official documentation，‘The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list. However, each table or view column in any nonaggregate expression in the list must be included in the GROUP BY list’， it will indicate the cause of your error.
For more details , you can refer to this link and you will see some examples about your issue: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017#arguments
There is a great example to explain for you : https://www.codeproject.com/Articles/1110163/%2FArticles%2F1110163%2FSQL-GROUP-By-and-the-Column-name-is-invalid-in-the
answered Jan 2 at 8:36
Rachel_WangRachel_Wang
243
add a comment |
According to the official documentation，‘The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list. However, each table or view column in any nonaggregate expression in the list must be included in the GROUP BY list’， it will indicate the cause of your error.
For more details , you can refer to this link and you will see some examples about your issue: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017#arguments
There is a great example to explain for you : https://www.codeproject.com/Articles/1110163/%2FArticles%2F1110163%2FSQL-GROUP-By-and-the-Column-name-is-invalid-in-the
answered Jan 2 at 8:36
Rachel_WangRachel_Wang
243
According to the official documentation，‘The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list. However, each table or view column in any nonaggregate expression in the list must be included in the GROUP BY list’， it will indicate the cause of your error.
For more details , you can refer to this link and you will see some examples about your issue: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017#arguments
There is a great example to explain for you : https://www.codeproject.com/Articles/1110163/%2FArticles%2F1110163%2FSQL-GROUP-By-and-the-Column-name-is-invalid-in-the
answered Jan 2 at 8:36
Rachel_WangRachel_Wang
243
answered Jan 2 at 8:36
Rachel_WangRachel_Wang
243
answered Jan 2 at 8:36
Rachel_WangRachel_Wang
243
answered Jan 2 at 8:36
Rachel_WangRachel_Wang
243
243
add a comment |
add a comment |
Here we have to revise the Concept of using the Group By and Order By in A SQL Query.
Remember One thing the name of column that has been came in Order By cannot be used inside aggregate function such as SUM, Average or MAX.
So don't put Same Column Name in Aggregate Function and with Order By too.
answered Jan 13 at 14:35
Abdur Rehman KhalidAbdur Rehman Khalid
315
add a comment |
Here we have to revise the Concept of using the Group By and Order By in A SQL Query.
Remember One thing the name of column that has been came in Order By cannot be used inside aggregate function such as SUM, Average or MAX.
So don't put Same Column Name in Aggregate Function and with Order By too.
answered Jan 13 at 14:35
Abdur Rehman KhalidAbdur Rehman Khalid
315
add a comment |
Here we have to revise the Concept of using the Group By and Order By in A SQL Query.
Remember One thing the name of column that has been came in Order By cannot be used inside aggregate function such as SUM, Average or MAX.
So don't put Same Column Name in Aggregate Function and with Order By too.
answered Jan 13 at 14:35
Abdur Rehman KhalidAbdur Rehman Khalid
315
Here we have to revise the Concept of using the Group By and Order By in A SQL Query.
Remember One thing the name of column that has been came in Order By cannot be used inside aggregate function such as SUM, Average or MAX.
So don't put Same Column Name in Aggregate Function and with Order By too.
answered Jan 13 at 14:35
Abdur Rehman KhalidAbdur Rehman Khalid
315
answered Jan 13 at 14:35
Abdur Rehman KhalidAbdur Rehman Khalid
315
answered Jan 13 at 14:35
Abdur Rehman KhalidAbdur Rehman Khalid
315
answered Jan 13 at 14:35
Abdur Rehman KhalidAbdur Rehman Khalid
315
315
add a comment |
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%2f53975404%2faggregate-function-error-while-using-group-by-clause-in-sql%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
3
billdatecolumn has to either be in aggregated (sum,max, etc.) or included in thegroup byclause.– tarheel
Dec 30 '18 at 5:25
If I include the billdate column in group by I'm not getting the desired result @tarheel
– Hari Ram Str
Dec 30 '18 at 5:55
2
Then you'll need to aggregate it. For more specific help, please provide sample data and expected output.
– tarheel
Dec 30 '18 at 6:03