sql mode only full group by
up vote
0
down vote
favorite
I have a problem with sql_mode=only_full_group_by . This problem will be come some windows computer but some other computer and server i didn't get any related problem.
So i get the sql_mode=only_full_group_by error from the following code:
$yearMonthTotalyUsers = array(0,0,0,0,0,0,0,0,0,0,0,0);
$YearMonthyTotalyUser = mysqli_query($db,"SELECT MONTH(FROM_UNIXTIME(user_registered))+1, count(*)
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by MONTH(FROM_UNIXTIME(user_registered))
ORDER BY MONTH(FROM_UNIXTIME(user_registered))
") or die(mysqli_error($db));
while ($row = mysqli_fetch_array($YearMonthyTotalyUser, MYSQLI_NUM)) {
$yearMonthTotalyUsers[$row[0]] = $row[1];
}
anyone can help me why i am getting this problem and how to fix it?
Here is the full Error Message:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'data.dot_users.user_registered' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
php sql
asked Nov 11 at 22:01
Hüseyin Ak
61
add a comment |
up vote
0
down vote
favorite
I have a problem with sql_mode=only_full_group_by . This problem will be come some windows computer but some other computer and server i didn't get any related problem.
So i get the sql_mode=only_full_group_by error from the following code:
$yearMonthTotalyUsers = array(0,0,0,0,0,0,0,0,0,0,0,0);
$YearMonthyTotalyUser = mysqli_query($db,"SELECT MONTH(FROM_UNIXTIME(user_registered))+1, count(*)
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by MONTH(FROM_UNIXTIME(user_registered))
ORDER BY MONTH(FROM_UNIXTIME(user_registered))
") or die(mysqli_error($db));
while ($row = mysqli_fetch_array($YearMonthyTotalyUser, MYSQLI_NUM)) {
$yearMonthTotalyUsers[$row[0]] = $row[1];
}
anyone can help me why i am getting this problem and how to fix it?
Here is the full Error Message:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'data.dot_users.user_registered' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
php sql
asked Nov 11 at 22:01
Hüseyin Ak
61
@ErayBalkanli I have added full error message in the question.
– Hüseyin Ak
Nov 11 at 22:07
1
I am really surprised that full group by mode does not support expressions on thegroup bykeys.
– Gordon Linoff
Nov 11 at 23:17
1
"I am really surprised that full group by mode does not support expressions on the group by keys. " Yes me too @GordonLinoff because i could not reprodure it.. MySQL seams to support expressions with sql mode only full group by db-fiddle.com/f/ohFmo6ThCbYVJADfp1kNdi/0 from MySQL 5.5 to MySQL 8 just fine .. looks like the topicstarter MySQL version is bugged or something like that..
– Raymond Nijland
Nov 12 at 10:26
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a problem with sql_mode=only_full_group_by . This problem will be come some windows computer but some other computer and server i didn't get any related problem.
So i get the sql_mode=only_full_group_by error from the following code:
$yearMonthTotalyUsers = array(0,0,0,0,0,0,0,0,0,0,0,0);
$YearMonthyTotalyUser = mysqli_query($db,"SELECT MONTH(FROM_UNIXTIME(user_registered))+1, count(*)
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by MONTH(FROM_UNIXTIME(user_registered))
ORDER BY MONTH(FROM_UNIXTIME(user_registered))
") or die(mysqli_error($db));
while ($row = mysqli_fetch_array($YearMonthyTotalyUser, MYSQLI_NUM)) {
$yearMonthTotalyUsers[$row[0]] = $row[1];
}
anyone can help me why i am getting this problem and how to fix it?
Here is the full Error Message:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'data.dot_users.user_registered' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
php sql
asked Nov 11 at 22:01
Hüseyin Ak
61
I have a problem with sql_mode=only_full_group_by . This problem will be come some windows computer but some other computer and server i didn't get any related problem.
So i get the sql_mode=only_full_group_by error from the following code:
$yearMonthTotalyUsers = array(0,0,0,0,0,0,0,0,0,0,0,0);
$YearMonthyTotalyUser = mysqli_query($db,"SELECT MONTH(FROM_UNIXTIME(user_registered))+1, count(*)
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by MONTH(FROM_UNIXTIME(user_registered))
ORDER BY MONTH(FROM_UNIXTIME(user_registered))
") or die(mysqli_error($db));
while ($row = mysqli_fetch_array($YearMonthyTotalyUser, MYSQLI_NUM)) {
$yearMonthTotalyUsers[$row[0]] = $row[1];
}
anyone can help me why i am getting this problem and how to fix it?
Here is the full Error Message:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'data.dot_users.user_registered' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
php sql
php sql
asked Nov 11 at 22:01
Hüseyin Ak
61
asked Nov 11 at 22:01
Hüseyin Ak
61
edited Nov 11 at 22:07
asked Nov 11 at 22:01
Hüseyin Ak
61
asked Nov 11 at 22:01
Hüseyin Ak
61
asked Nov 11 at 22:01
Hüseyin Ak
61
61
@ErayBalkanli I have added full error message in the question.
– Hüseyin Ak
Nov 11 at 22:07
1
I am really surprised that full group by mode does not support expressions on thegroup bykeys.
– Gordon Linoff
Nov 11 at 23:17
1
"I am really surprised that full group by mode does not support expressions on the group by keys. " Yes me too @GordonLinoff because i could not reprodure it.. MySQL seams to support expressions with sql mode only full group by db-fiddle.com/f/ohFmo6ThCbYVJADfp1kNdi/0 from MySQL 5.5 to MySQL 8 just fine .. looks like the topicstarter MySQL version is bugged or something like that..
– Raymond Nijland
Nov 12 at 10:26
add a comment |
@ErayBalkanli I have added full error message in the question.
– Hüseyin Ak
Nov 11 at 22:07
1
I am really surprised that full group by mode does not support expressions on thegroup bykeys.
– Gordon Linoff
Nov 11 at 23:17
1
"I am really surprised that full group by mode does not support expressions on the group by keys. " Yes me too @GordonLinoff because i could not reprodure it.. MySQL seams to support expressions with sql mode only full group by db-fiddle.com/f/ohFmo6ThCbYVJADfp1kNdi/0 from MySQL 5.5 to MySQL 8 just fine .. looks like the topicstarter MySQL version is bugged or something like that..
– Raymond Nijland
Nov 12 at 10:26
@ErayBalkanli I have added full error message in the question.
– Hüseyin Ak
Nov 11 at 22:07
@ErayBalkanli I have added full error message in the question.
– Hüseyin Ak
Nov 11 at 22:07
1
1
I am really surprised that full group by mode does not support expressions on the
group by keys.– Gordon Linoff
Nov 11 at 23:17
I am really surprised that full group by mode does not support expressions on the
group by keys.– Gordon Linoff
Nov 11 at 23:17
1
1
"I am really surprised that full group by mode does not support expressions on the group by keys. " Yes me too @GordonLinoff because i could not reprodure it.. MySQL seams to support expressions with sql mode only full group by db-fiddle.com/f/ohFmo6ThCbYVJADfp1kNdi/0 from MySQL 5.5 to MySQL 8 just fine .. looks like the topicstarter MySQL version is bugged or something like that..
– Raymond Nijland
Nov 12 at 10:26
"I am really surprised that full group by mode does not support expressions on the group by keys. " Yes me too @GordonLinoff because i could not reprodure it.. MySQL seams to support expressions with sql mode only full group by db-fiddle.com/f/ohFmo6ThCbYVJADfp1kNdi/0 from MySQL 5.5 to MySQL 8 just fine .. looks like the topicstarter MySQL version is bugged or something like that..
– Raymond Nijland
Nov 12 at 10:26
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
Your MySQL version seams "bugged" that it requires the SELECT column name/alias/expression to be the same as the GROUP BY with sql mode ONLY_FULL_GROUP_BY
You can try to wrap it in a delivered table /subquery approach.
SELECT
user_registered_month + 1
, `count`
FROM (
SELECT MONTH(FROM_UNIXTIME(user_registered)) AS user_registered_month, count(*) AS `count`
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by MONTH(FROM_UNIXTIME(user_registered))
ORDER BY MONTH(FROM_UNIXTIME(user_registered))
) AS alias
Or if the database supports it this is also possible, MySQL for example supports this kind of reusing of aliases
SELECT
user_registered_month + 1
, `count`
FROM (
SELECT MONTH(FROM_UNIXTIME(user_registered)) AS user_registered_month, count(*) AS `count`
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by user_registered_month
ORDER BY user_registered_month
) AS alias
answered Nov 11 at 22:38
Raymond Nijland
7,76921327
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%2f53253684%2fsql-mode-only-full-group-by%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
1
down vote
Your MySQL version seams "bugged" that it requires the SELECT column name/alias/expression to be the same as the GROUP BY with sql mode ONLY_FULL_GROUP_BY
You can try to wrap it in a delivered table /subquery approach.
SELECT
user_registered_month + 1
, `count`
FROM (
SELECT MONTH(FROM_UNIXTIME(user_registered)) AS user_registered_month, count(*) AS `count`
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by MONTH(FROM_UNIXTIME(user_registered))
ORDER BY MONTH(FROM_UNIXTIME(user_registered))
) AS alias
Or if the database supports it this is also possible, MySQL for example supports this kind of reusing of aliases
SELECT
user_registered_month + 1
, `count`
FROM (
SELECT MONTH(FROM_UNIXTIME(user_registered)) AS user_registered_month, count(*) AS `count`
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by user_registered_month
ORDER BY user_registered_month
) AS alias
answered Nov 11 at 22:38
Raymond Nijland
7,76921327
add a comment |
up vote
1
down vote
Your MySQL version seams "bugged" that it requires the SELECT column name/alias/expression to be the same as the GROUP BY with sql mode ONLY_FULL_GROUP_BY
You can try to wrap it in a delivered table /subquery approach.
SELECT
user_registered_month + 1
, `count`
FROM (
SELECT MONTH(FROM_UNIXTIME(user_registered)) AS user_registered_month, count(*) AS `count`
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by MONTH(FROM_UNIXTIME(user_registered))
ORDER BY MONTH(FROM_UNIXTIME(user_registered))
) AS alias
Or if the database supports it this is also possible, MySQL for example supports this kind of reusing of aliases
SELECT
user_registered_month + 1
, `count`
FROM (
SELECT MONTH(FROM_UNIXTIME(user_registered)) AS user_registered_month, count(*) AS `count`
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by user_registered_month
ORDER BY user_registered_month
) AS alias
answered Nov 11 at 22:38
Raymond Nijland
7,76921327
add a comment |
up vote
1
down vote
up vote
1
down vote
Your MySQL version seams "bugged" that it requires the SELECT column name/alias/expression to be the same as the GROUP BY with sql mode ONLY_FULL_GROUP_BY
You can try to wrap it in a delivered table /subquery approach.
SELECT
user_registered_month + 1
, `count`
FROM (
SELECT MONTH(FROM_UNIXTIME(user_registered)) AS user_registered_month, count(*) AS `count`
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by MONTH(FROM_UNIXTIME(user_registered))
ORDER BY MONTH(FROM_UNIXTIME(user_registered))
) AS alias
Or if the database supports it this is also possible, MySQL for example supports this kind of reusing of aliases
SELECT
user_registered_month + 1
, `count`
FROM (
SELECT MONTH(FROM_UNIXTIME(user_registered)) AS user_registered_month, count(*) AS `count`
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by user_registered_month
ORDER BY user_registered_month
) AS alias
answered Nov 11 at 22:38
Raymond Nijland
7,76921327
Your MySQL version seams "bugged" that it requires the SELECT column name/alias/expression to be the same as the GROUP BY with sql mode ONLY_FULL_GROUP_BY
You can try to wrap it in a delivered table /subquery approach.
SELECT
user_registered_month + 1
, `count`
FROM (
SELECT MONTH(FROM_UNIXTIME(user_registered)) AS user_registered_month, count(*) AS `count`
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by MONTH(FROM_UNIXTIME(user_registered))
ORDER BY MONTH(FROM_UNIXTIME(user_registered))
) AS alias
Or if the database supports it this is also possible, MySQL for example supports this kind of reusing of aliases
SELECT
user_registered_month + 1
, `count`
FROM (
SELECT MONTH(FROM_UNIXTIME(user_registered)) AS user_registered_month, count(*) AS `count`
FROM dot_users
WHERE YEAR(FROM_UNIXTIME(user_registered)) = YEAR(CURDATE())
group by user_registered_month
ORDER BY user_registered_month
) AS alias
answered Nov 11 at 22:38
Raymond Nijland
7,76921327
edited Nov 12 at 10:26
answered Nov 11 at 22:38
Raymond Nijland
7,76921327
answered Nov 11 at 22:38
Raymond Nijland
7,76921327
answered Nov 11 at 22:38
Raymond Nijland
7,76921327
7,76921327
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.
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.
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%2f53253684%2fsql-mode-only-full-group-by%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
@ErayBalkanli I have added full error message in the question.
– Hüseyin Ak
Nov 11 at 22:07
1
I am really surprised that full group by mode does not support expressions on the
group bykeys.– Gordon Linoff
Nov 11 at 23:17
1
"I am really surprised that full group by mode does not support expressions on the group by keys. " Yes me too @GordonLinoff because i could not reprodure it.. MySQL seams to support expressions with sql mode only full group by db-fiddle.com/f/ohFmo6ThCbYVJADfp1kNdi/0 from MySQL 5.5 to MySQL 8 just fine .. looks like the topicstarter MySQL version is bugged or something like that..
– Raymond Nijland
Nov 12 at 10:26