When I use “WHERE user_id in ( sub query )” generate syntax error
I have a users table used below.
Users have referal_code, refered_by columns.Users has following data.
+----+--------------+------------+
| id | referal_code | refered_by |
+----+--------------+------------+
| 1 | abc | null |
| 2 | xxx | abc |
+----+--------------+------------+
I have Reviews table in which I store users reviewe by other users.
It does have user_id, evaluation columns.
+----+---------+------------+
| id | user_id | evaluation |
+----+---------+------------+
| 28 | 2 | 4 |
| 32 | 2 | 6 |
+----+---------+------------+
I'm trying to count users referred by each user have an average evaluation of 3 or more.
SELECT users.*, COUNT(
SELECT reviews.user_id FROM reviews
WHERE reviews.user_id IN(
SELECT A2.id FROM users as A2 WHERE A2.refered_by = users.referal_code
)
HAVING AVG(evaluation) >= 3) as total_3_estrelas
FROM users
WHERE 1
I have a syntax error #1064 on: WHERE user_id IN
The result I expect:
+----+--------------+------------+------------------+
| id | referal_code | refered_by | total_3_estrelas |
+----+--------------+------------+------------------+
| 1 | abc | null | 1 |
| 2 | xxx | abc | 0 |
+----+--------------+------------+------------------+
mysql
edited Nov 28 '18 at 10:12
Vishal Parmar
13715
asked Nov 28 '18 at 7:45
Carlos BrancoCarlos Branco
31211
add a comment |
I have a users table used below.
Users have referal_code, refered_by columns.Users has following data.
+----+--------------+------------+
| id | referal_code | refered_by |
+----+--------------+------------+
| 1 | abc | null |
| 2 | xxx | abc |
+----+--------------+------------+
I have Reviews table in which I store users reviewe by other users.
It does have user_id, evaluation columns.
+----+---------+------------+
| id | user_id | evaluation |
+----+---------+------------+
| 28 | 2 | 4 |
| 32 | 2 | 6 |
+----+---------+------------+
I'm trying to count users referred by each user have an average evaluation of 3 or more.
SELECT users.*, COUNT(
SELECT reviews.user_id FROM reviews
WHERE reviews.user_id IN(
SELECT A2.id FROM users as A2 WHERE A2.refered_by = users.referal_code
)
HAVING AVG(evaluation) >= 3) as total_3_estrelas
FROM users
WHERE 1
I have a syntax error #1064 on: WHERE user_id IN
The result I expect:
+----+--------------+------------+------------------+
| id | referal_code | refered_by | total_3_estrelas |
+----+--------------+------------+------------------+
| 1 | abc | null | 1 |
| 2 | xxx | abc | 0 |
+----+--------------+------------+------------------+
mysql
edited Nov 28 '18 at 10:12
Vishal Parmar
13715
asked Nov 28 '18 at 7:45
Carlos BrancoCarlos Branco
31211
I cannot find a reference for this but mysql does not like a select clause in an aggregate function.
– P.Salmon
Nov 28 '18 at 8:14
The result is: user id 1 have one referral that an average evaluation of 3 or more. user id 2 have 0 referrals that have an evaluation 3 or more.
– Carlos Branco
Nov 28 '18 at 9:20
How from this data do you know that 2 was reviewed by 1?
– P.Salmon
Nov 28 '18 at 9:25
can be reviewed by anyone. I just want to know: How many of each user referrals have been reviewed with an AVG of 3 or more. Anyone @Gufus code works. Thank you for help.
– Carlos Branco
Nov 28 '18 at 9:37
add a comment |
I have a users table used below.
Users have referal_code, refered_by columns.Users has following data.
+----+--------------+------------+
| id | referal_code | refered_by |
+----+--------------+------------+
| 1 | abc | null |
| 2 | xxx | abc |
+----+--------------+------------+
I have Reviews table in which I store users reviewe by other users.
It does have user_id, evaluation columns.
+----+---------+------------+
| id | user_id | evaluation |
+----+---------+------------+
| 28 | 2 | 4 |
| 32 | 2 | 6 |
+----+---------+------------+
I'm trying to count users referred by each user have an average evaluation of 3 or more.
SELECT users.*, COUNT(
SELECT reviews.user_id FROM reviews
WHERE reviews.user_id IN(
SELECT A2.id FROM users as A2 WHERE A2.refered_by = users.referal_code
)
HAVING AVG(evaluation) >= 3) as total_3_estrelas
FROM users
WHERE 1
I have a syntax error #1064 on: WHERE user_id IN
The result I expect:
+----+--------------+------------+------------------+
| id | referal_code | refered_by | total_3_estrelas |
+----+--------------+------------+------------------+
| 1 | abc | null | 1 |
| 2 | xxx | abc | 0 |
+----+--------------+------------+------------------+
mysql
edited Nov 28 '18 at 10:12
Vishal Parmar
13715
asked Nov 28 '18 at 7:45
Carlos BrancoCarlos Branco
31211
I have a users table used below.
Users have referal_code, refered_by columns.Users has following data.
+----+--------------+------------+
| id | referal_code | refered_by |
+----+--------------+------------+
| 1 | abc | null |
| 2 | xxx | abc |
+----+--------------+------------+
I have Reviews table in which I store users reviewe by other users.
It does have user_id, evaluation columns.
+----+---------+------------+
| id | user_id | evaluation |
+----+---------+------------+
| 28 | 2 | 4 |
| 32 | 2 | 6 |
+----+---------+------------+
I'm trying to count users referred by each user have an average evaluation of 3 or more.
SELECT users.*, COUNT(
SELECT reviews.user_id FROM reviews
WHERE reviews.user_id IN(
SELECT A2.id FROM users as A2 WHERE A2.refered_by = users.referal_code
)
HAVING AVG(evaluation) >= 3) as total_3_estrelas
FROM users
WHERE 1
I have a syntax error #1064 on: WHERE user_id IN
The result I expect:
+----+--------------+------------+------------------+
| id | referal_code | refered_by | total_3_estrelas |
+----+--------------+------------+------------------+
| 1 | abc | null | 1 |
| 2 | xxx | abc | 0 |
+----+--------------+------------+------------------+
mysql
mysql
edited Nov 28 '18 at 10:12
Vishal Parmar
13715
asked Nov 28 '18 at 7:45
Carlos BrancoCarlos Branco
31211
edited Nov 28 '18 at 10:12
Vishal Parmar
13715
asked Nov 28 '18 at 7:45
Carlos BrancoCarlos Branco
31211
edited Nov 28 '18 at 10:12
Vishal Parmar
13715
edited Nov 28 '18 at 10:12
Vishal Parmar
13715
edited Nov 28 '18 at 10:12
Vishal Parmar
13715
13715
asked Nov 28 '18 at 7:45
Carlos BrancoCarlos Branco
31211
asked Nov 28 '18 at 7:45
Carlos BrancoCarlos Branco
31211
asked Nov 28 '18 at 7:45
Carlos BrancoCarlos Branco
31211
31211
I cannot find a reference for this but mysql does not like a select clause in an aggregate function.
– P.Salmon
Nov 28 '18 at 8:14
The result is: user id 1 have one referral that an average evaluation of 3 or more. user id 2 have 0 referrals that have an evaluation 3 or more.
– Carlos Branco
Nov 28 '18 at 9:20
How from this data do you know that 2 was reviewed by 1?
– P.Salmon
Nov 28 '18 at 9:25
can be reviewed by anyone. I just want to know: How many of each user referrals have been reviewed with an AVG of 3 or more. Anyone @Gufus code works. Thank you for help.
– Carlos Branco
Nov 28 '18 at 9:37
add a comment |
I cannot find a reference for this but mysql does not like a select clause in an aggregate function.
– P.Salmon
Nov 28 '18 at 8:14
The result is: user id 1 have one referral that an average evaluation of 3 or more. user id 2 have 0 referrals that have an evaluation 3 or more.
– Carlos Branco
Nov 28 '18 at 9:20
How from this data do you know that 2 was reviewed by 1?
– P.Salmon
Nov 28 '18 at 9:25
can be reviewed by anyone. I just want to know: How many of each user referrals have been reviewed with an AVG of 3 or more. Anyone @Gufus code works. Thank you for help.
– Carlos Branco
Nov 28 '18 at 9:37
I cannot find a reference for this but mysql does not like a select clause in an aggregate function.
– P.Salmon
Nov 28 '18 at 8:14
I cannot find a reference for this but mysql does not like a select clause in an aggregate function.
– P.Salmon
Nov 28 '18 at 8:14
The result is: user id 1 have one referral that an average evaluation of 3 or more. user id 2 have 0 referrals that have an evaluation 3 or more.
– Carlos Branco
Nov 28 '18 at 9:20
The result is: user id 1 have one referral that an average evaluation of 3 or more. user id 2 have 0 referrals that have an evaluation 3 or more.
– Carlos Branco
Nov 28 '18 at 9:20
How from this data do you know that 2 was reviewed by 1?
– P.Salmon
Nov 28 '18 at 9:25
How from this data do you know that 2 was reviewed by 1?
– P.Salmon
Nov 28 '18 at 9:25
can be reviewed by anyone. I just want to know: How many of each user referrals have been reviewed with an AVG of 3 or more. Anyone @Gufus code works. Thank you for help.
– Carlos Branco
Nov 28 '18 at 9:37
can be reviewed by anyone. I just want to know: How many of each user referrals have been reviewed with an AVG of 3 or more. Anyone @Gufus code works. Thank you for help.
– Carlos Branco
Nov 28 '18 at 9:37
add a comment |
1 Answer
1
active
oldest
votes
Look at this if it helps:
SELECT A.ID, A.REFERAL_CODE, A.REFERED_BY, COALESCE(TOTAL_3_ESTRELAS,0) AS TOTAL_3_ESTRELAS
FROM USERS A
LEFT JOIN
(SELECT REFERED_BY, COUNT(*) AS TOTAL_3_ESTRELAS
FROM USERS U
INNER JOIN (SELECT USER_ID, AVG(EVALUATION)
FROM REVIEWS
GROUP BY USER_ID
HAVING AVG(EVALUATION)>=3) R
ON U.ID=R.USER_ID
GROUP BY REFERED_BY) T
ON A.REFERAL_CODE=T.REFERED_BY;
From the deeper nested condition, first I calculated the average evaluation for each user_id on REVIEWS throwing away USER_ID with avg below 3, then I made the inner join with USERS and I grouped by REFERED_BY to obtain the count desired. Finally I did a left join to obtain the output in the form you expect.
answered Nov 28 '18 at 9:14
GufusGufus
138119
It works. Thank you!
– Carlos Branco
Nov 28 '18 at 9:37
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%2f53514484%2fwhen-i-use-where-user-id-in-sub-query-generate-syntax-error%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
Look at this if it helps:
SELECT A.ID, A.REFERAL_CODE, A.REFERED_BY, COALESCE(TOTAL_3_ESTRELAS,0) AS TOTAL_3_ESTRELAS
FROM USERS A
LEFT JOIN
(SELECT REFERED_BY, COUNT(*) AS TOTAL_3_ESTRELAS
FROM USERS U
INNER JOIN (SELECT USER_ID, AVG(EVALUATION)
FROM REVIEWS
GROUP BY USER_ID
HAVING AVG(EVALUATION)>=3) R
ON U.ID=R.USER_ID
GROUP BY REFERED_BY) T
ON A.REFERAL_CODE=T.REFERED_BY;
From the deeper nested condition, first I calculated the average evaluation for each user_id on REVIEWS throwing away USER_ID with avg below 3, then I made the inner join with USERS and I grouped by REFERED_BY to obtain the count desired. Finally I did a left join to obtain the output in the form you expect.
answered Nov 28 '18 at 9:14
GufusGufus
138119
It works. Thank you!
– Carlos Branco
Nov 28 '18 at 9:37
add a comment |
Look at this if it helps:
SELECT A.ID, A.REFERAL_CODE, A.REFERED_BY, COALESCE(TOTAL_3_ESTRELAS,0) AS TOTAL_3_ESTRELAS
FROM USERS A
LEFT JOIN
(SELECT REFERED_BY, COUNT(*) AS TOTAL_3_ESTRELAS
FROM USERS U
INNER JOIN (SELECT USER_ID, AVG(EVALUATION)
FROM REVIEWS
GROUP BY USER_ID
HAVING AVG(EVALUATION)>=3) R
ON U.ID=R.USER_ID
GROUP BY REFERED_BY) T
ON A.REFERAL_CODE=T.REFERED_BY;
From the deeper nested condition, first I calculated the average evaluation for each user_id on REVIEWS throwing away USER_ID with avg below 3, then I made the inner join with USERS and I grouped by REFERED_BY to obtain the count desired. Finally I did a left join to obtain the output in the form you expect.
answered Nov 28 '18 at 9:14
GufusGufus
138119
It works. Thank you!
– Carlos Branco
Nov 28 '18 at 9:37
add a comment |
Look at this if it helps:
SELECT A.ID, A.REFERAL_CODE, A.REFERED_BY, COALESCE(TOTAL_3_ESTRELAS,0) AS TOTAL_3_ESTRELAS
FROM USERS A
LEFT JOIN
(SELECT REFERED_BY, COUNT(*) AS TOTAL_3_ESTRELAS
FROM USERS U
INNER JOIN (SELECT USER_ID, AVG(EVALUATION)
FROM REVIEWS
GROUP BY USER_ID
HAVING AVG(EVALUATION)>=3) R
ON U.ID=R.USER_ID
GROUP BY REFERED_BY) T
ON A.REFERAL_CODE=T.REFERED_BY;
From the deeper nested condition, first I calculated the average evaluation for each user_id on REVIEWS throwing away USER_ID with avg below 3, then I made the inner join with USERS and I grouped by REFERED_BY to obtain the count desired. Finally I did a left join to obtain the output in the form you expect.
answered Nov 28 '18 at 9:14
GufusGufus
138119
Look at this if it helps:
SELECT A.ID, A.REFERAL_CODE, A.REFERED_BY, COALESCE(TOTAL_3_ESTRELAS,0) AS TOTAL_3_ESTRELAS
FROM USERS A
LEFT JOIN
(SELECT REFERED_BY, COUNT(*) AS TOTAL_3_ESTRELAS
FROM USERS U
INNER JOIN (SELECT USER_ID, AVG(EVALUATION)
FROM REVIEWS
GROUP BY USER_ID
HAVING AVG(EVALUATION)>=3) R
ON U.ID=R.USER_ID
GROUP BY REFERED_BY) T
ON A.REFERAL_CODE=T.REFERED_BY;
From the deeper nested condition, first I calculated the average evaluation for each user_id on REVIEWS throwing away USER_ID with avg below 3, then I made the inner join with USERS and I grouped by REFERED_BY to obtain the count desired. Finally I did a left join to obtain the output in the form you expect.
answered Nov 28 '18 at 9:14
GufusGufus
138119
answered Nov 28 '18 at 9:14
GufusGufus
138119
answered Nov 28 '18 at 9:14
GufusGufus
138119
answered Nov 28 '18 at 9:14
GufusGufus
138119
138119
It works. Thank you!
– Carlos Branco
Nov 28 '18 at 9:37
add a comment |
It works. Thank you!
– Carlos Branco
Nov 28 '18 at 9:37
It works. Thank you!
– Carlos Branco
Nov 28 '18 at 9:37
It works. Thank you!
– Carlos Branco
Nov 28 '18 at 9:37
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%2f53514484%2fwhen-i-use-where-user-id-in-sub-query-generate-syntax-error%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
I cannot find a reference for this but mysql does not like a select clause in an aggregate function.
– P.Salmon
Nov 28 '18 at 8:14
The result is: user id 1 have one referral that an average evaluation of 3 or more. user id 2 have 0 referrals that have an evaluation 3 or more.
– Carlos Branco
Nov 28 '18 at 9:20
How from this data do you know that 2 was reviewed by 1?
– P.Salmon
Nov 28 '18 at 9:25
can be reviewed by anyone. I just want to know: How many of each user referrals have been reviewed with an AVG of 3 or more. Anyone @Gufus code works. Thank you for help.
– Carlos Branco
Nov 28 '18 at 9:37