MySQL: Concatenate two information, return a virtual column and in sequence perform an INNER JOIN with the...
0
In my project, I need make a Column with Concat information, like this:
CONCAT('SIP/', name) AS sipAgent.
And this works, but when i try to make a INNER JOIN with the column sipAgent, has error :(
And after inner join i need to SUM the results for each result of
like that:
----------------------------------
| NAME | sipAgent | notAnswered |
----------------------------------
| aaa | SIP/aaa | 132 |
| bbb | SIP/bbb | 50 |
----------------------------------
Name is the reference of agent, sipAgent is 'SIP/' + name, and noAnswered is the number of rows returned in a inner join.
Here is my 'test-query':
SELECT *, CONCAT('SIP/', tab_sippeers.name) AS sipAgent, SUM(queue_log.event) as notAnswered
FROM 'ipbx.tab_sippeers'
join 'queue_log' on tab_sippeers.sipAgent = queue_log.agent
But, is returning error...
Sorry for bad english, and thanks alot for help!
mysql join sum concat
asked Nov 22 '18 at 12:26
Rodrigo Roberto de AlmeidaRodrigo Roberto de Almeida
34
add a comment |
0
In my project, I need make a Column with Concat information, like this:
CONCAT('SIP/', name) AS sipAgent.
And this works, but when i try to make a INNER JOIN with the column sipAgent, has error :(
And after inner join i need to SUM the results for each result of
like that:
----------------------------------
| NAME | sipAgent | notAnswered |
----------------------------------
| aaa | SIP/aaa | 132 |
| bbb | SIP/bbb | 50 |
----------------------------------
Name is the reference of agent, sipAgent is 'SIP/' + name, and noAnswered is the number of rows returned in a inner join.
Here is my 'test-query':
SELECT *, CONCAT('SIP/', tab_sippeers.name) AS sipAgent, SUM(queue_log.event) as notAnswered
FROM 'ipbx.tab_sippeers'
join 'queue_log' on tab_sippeers.sipAgent = queue_log.agent
But, is returning error...
Sorry for bad english, and thanks alot for help!
mysql join sum concat
asked Nov 22 '18 at 12:26
Rodrigo Roberto de AlmeidaRodrigo Roberto de Almeida
34
edit your question with error also.
– Madhur Sharma
Nov 22 '18 at 12:29
It is noteworthy that we cannot refer to evaluated expression aliases in theONclause ofJOIN.
– Madhur Bhaiya
Nov 22 '18 at 12:57
add a comment |
0
0
0
In my project, I need make a Column with Concat information, like this:
CONCAT('SIP/', name) AS sipAgent.
And this works, but when i try to make a INNER JOIN with the column sipAgent, has error :(
And after inner join i need to SUM the results for each result of
like that:
----------------------------------
| NAME | sipAgent | notAnswered |
----------------------------------
| aaa | SIP/aaa | 132 |
| bbb | SIP/bbb | 50 |
----------------------------------
Name is the reference of agent, sipAgent is 'SIP/' + name, and noAnswered is the number of rows returned in a inner join.
Here is my 'test-query':
SELECT *, CONCAT('SIP/', tab_sippeers.name) AS sipAgent, SUM(queue_log.event) as notAnswered
FROM 'ipbx.tab_sippeers'
join 'queue_log' on tab_sippeers.sipAgent = queue_log.agent
But, is returning error...
Sorry for bad english, and thanks alot for help!
mysql join sum concat
asked Nov 22 '18 at 12:26
Rodrigo Roberto de AlmeidaRodrigo Roberto de Almeida
34
In my project, I need make a Column with Concat information, like this:
CONCAT('SIP/', name) AS sipAgent.
And this works, but when i try to make a INNER JOIN with the column sipAgent, has error :(
And after inner join i need to SUM the results for each result of
like that:
----------------------------------
| NAME | sipAgent | notAnswered |
----------------------------------
| aaa | SIP/aaa | 132 |
| bbb | SIP/bbb | 50 |
----------------------------------
Name is the reference of agent, sipAgent is 'SIP/' + name, and noAnswered is the number of rows returned in a inner join.
Here is my 'test-query':
SELECT *, CONCAT('SIP/', tab_sippeers.name) AS sipAgent, SUM(queue_log.event) as notAnswered
FROM 'ipbx.tab_sippeers'
join 'queue_log' on tab_sippeers.sipAgent = queue_log.agent
But, is returning error...
Sorry for bad english, and thanks alot for help!
mysql join sum concat
mysql join sum concat
asked Nov 22 '18 at 12:26
Rodrigo Roberto de AlmeidaRodrigo Roberto de Almeida
34
asked Nov 22 '18 at 12:26
Rodrigo Roberto de AlmeidaRodrigo Roberto de Almeida
34
asked Nov 22 '18 at 12:26
Rodrigo Roberto de AlmeidaRodrigo Roberto de Almeida
34
asked Nov 22 '18 at 12:26
Rodrigo Roberto de AlmeidaRodrigo Roberto de Almeida
34
asked Nov 22 '18 at 12:26
Rodrigo Roberto de AlmeidaRodrigo Roberto de Almeida
34
34
edit your question with error also.
– Madhur Sharma
Nov 22 '18 at 12:29
It is noteworthy that we cannot refer to evaluated expression aliases in theONclause ofJOIN.
– Madhur Bhaiya
Nov 22 '18 at 12:57
add a comment |
edit your question with error also.
– Madhur Sharma
Nov 22 '18 at 12:29
It is noteworthy that we cannot refer to evaluated expression aliases in theONclause ofJOIN.
– Madhur Bhaiya
Nov 22 '18 at 12:57
edit your question with error also.
– Madhur Sharma
Nov 22 '18 at 12:29
edit your question with error also.
– Madhur Sharma
Nov 22 '18 at 12:29
It is noteworthy that we cannot refer to evaluated expression aliases in the
ON clause of JOIN.– Madhur Bhaiya
Nov 22 '18 at 12:57
It is noteworthy that we cannot refer to evaluated expression aliases in the
ON clause of JOIN.– Madhur Bhaiya
Nov 22 '18 at 12:57
add a comment |
1 Answer
1
active
oldest
votes
1
Query doesn't look right in other ways but you could concat in the join
SELECT *, CONCAT('SIP/', tab_sippeers.name) AS sipAgent, SUM(queue_log.event) as notAnswered
FROM `ipbx.tab_sippeers`
join `queue_log` on CONCAT('SIP/', tab_sippeers.name) = queue_log.agent;
and table names and column names should be enclose in backticks not single quotes if you want to enclose them at all.
When to use single quotes, double quotes, and back ticks in MySQL
answered Nov 22 '18 at 12:50
P.SalmonP.Salmon
7,8872415
You can add this to answer: It is noteworthy that we cannot refer to evaluated expression aliases in the ON clause of JOIN.
– Madhur Bhaiya
Nov 22 '18 at 12:57
The query worked in parts ... it is now bringing the column 'notAnswered', but return is always 0. And it is always bringing 1 line of return, I would need to return all registered agents this information. I forgot to point out also that the notAnswered is the result count of: sipAgent where the results are queue_log.event = RINGNOANSWER
– Rodrigo Roberto de Almeida
Nov 22 '18 at 13:12
It would help if you added sample data and expected output as text to the question.
– P.Salmon
Nov 22 '18 at 13:19
If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows. - dev.mysql.com/doc/refman/8.0/en/group-by-functions.html
– P.Salmon
Nov 22 '18 at 13:21
works, thanks alot!
– Rodrigo Roberto de Almeida
Nov 23 '18 at 12:42
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
});
}
});
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%2f53431006%2fmysql-concatenate-two-information-return-a-virtual-column-and-in-sequence-perf%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
1
Query doesn't look right in other ways but you could concat in the join
SELECT *, CONCAT('SIP/', tab_sippeers.name) AS sipAgent, SUM(queue_log.event) as notAnswered
FROM `ipbx.tab_sippeers`
join `queue_log` on CONCAT('SIP/', tab_sippeers.name) = queue_log.agent;
and table names and column names should be enclose in backticks not single quotes if you want to enclose them at all.
When to use single quotes, double quotes, and back ticks in MySQL
answered Nov 22 '18 at 12:50
P.SalmonP.Salmon
7,8872415
You can add this to answer: It is noteworthy that we cannot refer to evaluated expression aliases in the ON clause of JOIN.
– Madhur Bhaiya
Nov 22 '18 at 12:57
The query worked in parts ... it is now bringing the column 'notAnswered', but return is always 0. And it is always bringing 1 line of return, I would need to return all registered agents this information. I forgot to point out also that the notAnswered is the result count of: sipAgent where the results are queue_log.event = RINGNOANSWER
– Rodrigo Roberto de Almeida
Nov 22 '18 at 13:12
It would help if you added sample data and expected output as text to the question.
– P.Salmon
Nov 22 '18 at 13:19
If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows. - dev.mysql.com/doc/refman/8.0/en/group-by-functions.html
– P.Salmon
Nov 22 '18 at 13:21
works, thanks alot!
– Rodrigo Roberto de Almeida
Nov 23 '18 at 12:42
add a comment |
1
Query doesn't look right in other ways but you could concat in the join
SELECT *, CONCAT('SIP/', tab_sippeers.name) AS sipAgent, SUM(queue_log.event) as notAnswered
FROM `ipbx.tab_sippeers`
join `queue_log` on CONCAT('SIP/', tab_sippeers.name) = queue_log.agent;
and table names and column names should be enclose in backticks not single quotes if you want to enclose them at all.
When to use single quotes, double quotes, and back ticks in MySQL
answered Nov 22 '18 at 12:50
P.SalmonP.Salmon
7,8872415
You can add this to answer: It is noteworthy that we cannot refer to evaluated expression aliases in the ON clause of JOIN.
– Madhur Bhaiya
Nov 22 '18 at 12:57
The query worked in parts ... it is now bringing the column 'notAnswered', but return is always 0. And it is always bringing 1 line of return, I would need to return all registered agents this information. I forgot to point out also that the notAnswered is the result count of: sipAgent where the results are queue_log.event = RINGNOANSWER
– Rodrigo Roberto de Almeida
Nov 22 '18 at 13:12
It would help if you added sample data and expected output as text to the question.
– P.Salmon
Nov 22 '18 at 13:19
If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows. - dev.mysql.com/doc/refman/8.0/en/group-by-functions.html
– P.Salmon
Nov 22 '18 at 13:21
works, thanks alot!
– Rodrigo Roberto de Almeida
Nov 23 '18 at 12:42
add a comment |
1
1
1
Query doesn't look right in other ways but you could concat in the join
SELECT *, CONCAT('SIP/', tab_sippeers.name) AS sipAgent, SUM(queue_log.event) as notAnswered
FROM `ipbx.tab_sippeers`
join `queue_log` on CONCAT('SIP/', tab_sippeers.name) = queue_log.agent;
and table names and column names should be enclose in backticks not single quotes if you want to enclose them at all.
When to use single quotes, double quotes, and back ticks in MySQL
answered Nov 22 '18 at 12:50
P.SalmonP.Salmon
7,8872415
Query doesn't look right in other ways but you could concat in the join
SELECT *, CONCAT('SIP/', tab_sippeers.name) AS sipAgent, SUM(queue_log.event) as notAnswered
FROM `ipbx.tab_sippeers`
join `queue_log` on CONCAT('SIP/', tab_sippeers.name) = queue_log.agent;
and table names and column names should be enclose in backticks not single quotes if you want to enclose them at all.
When to use single quotes, double quotes, and back ticks in MySQL
answered Nov 22 '18 at 12:50
P.SalmonP.Salmon
7,8872415
edited Nov 22 '18 at 12:55
answered Nov 22 '18 at 12:50
P.SalmonP.Salmon
7,8872415
answered Nov 22 '18 at 12:50
P.SalmonP.Salmon
7,8872415
answered Nov 22 '18 at 12:50
P.SalmonP.Salmon
7,8872415
7,8872415
You can add this to answer: It is noteworthy that we cannot refer to evaluated expression aliases in the ON clause of JOIN.
– Madhur Bhaiya
Nov 22 '18 at 12:57
The query worked in parts ... it is now bringing the column 'notAnswered', but return is always 0. And it is always bringing 1 line of return, I would need to return all registered agents this information. I forgot to point out also that the notAnswered is the result count of: sipAgent where the results are queue_log.event = RINGNOANSWER
– Rodrigo Roberto de Almeida
Nov 22 '18 at 13:12
It would help if you added sample data and expected output as text to the question.
– P.Salmon
Nov 22 '18 at 13:19
If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows. - dev.mysql.com/doc/refman/8.0/en/group-by-functions.html
– P.Salmon
Nov 22 '18 at 13:21
works, thanks alot!
– Rodrigo Roberto de Almeida
Nov 23 '18 at 12:42
add a comment |
You can add this to answer: It is noteworthy that we cannot refer to evaluated expression aliases in the ON clause of JOIN.
– Madhur Bhaiya
Nov 22 '18 at 12:57
The query worked in parts ... it is now bringing the column 'notAnswered', but return is always 0. And it is always bringing 1 line of return, I would need to return all registered agents this information. I forgot to point out also that the notAnswered is the result count of: sipAgent where the results are queue_log.event = RINGNOANSWER
– Rodrigo Roberto de Almeida
Nov 22 '18 at 13:12
It would help if you added sample data and expected output as text to the question.
– P.Salmon
Nov 22 '18 at 13:19
If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows. - dev.mysql.com/doc/refman/8.0/en/group-by-functions.html
– P.Salmon
Nov 22 '18 at 13:21
works, thanks alot!
– Rodrigo Roberto de Almeida
Nov 23 '18 at 12:42
You can add this to answer: It is noteworthy that we cannot refer to evaluated expression aliases in the ON clause of JOIN.
– Madhur Bhaiya
Nov 22 '18 at 12:57
You can add this to answer: It is noteworthy that we cannot refer to evaluated expression aliases in the ON clause of JOIN.
– Madhur Bhaiya
Nov 22 '18 at 12:57
The query worked in parts ... it is now bringing the column 'notAnswered', but return is always 0. And it is always bringing 1 line of return, I would need to return all registered agents this information. I forgot to point out also that the notAnswered is the result count of: sipAgent where the results are queue_log.event = RINGNOANSWER
– Rodrigo Roberto de Almeida
Nov 22 '18 at 13:12
The query worked in parts ... it is now bringing the column 'notAnswered', but return is always 0. And it is always bringing 1 line of return, I would need to return all registered agents this information. I forgot to point out also that the notAnswered is the result count of: sipAgent where the results are queue_log.event = RINGNOANSWER
– Rodrigo Roberto de Almeida
Nov 22 '18 at 13:12
It would help if you added sample data and expected output as text to the question.
– P.Salmon
Nov 22 '18 at 13:19
It would help if you added sample data and expected output as text to the question.
– P.Salmon
Nov 22 '18 at 13:19
If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows. - dev.mysql.com/doc/refman/8.0/en/group-by-functions.html
– P.Salmon
Nov 22 '18 at 13:21
If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows. - dev.mysql.com/doc/refman/8.0/en/group-by-functions.html
– P.Salmon
Nov 22 '18 at 13:21
works, thanks alot!
– Rodrigo Roberto de Almeida
Nov 23 '18 at 12:42
works, thanks alot!
– Rodrigo Roberto de Almeida
Nov 23 '18 at 12:42
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.
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%2f53431006%2fmysql-concatenate-two-information-return-a-virtual-column-and-in-sequence-perf%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
edit your question with error also.
– Madhur Sharma
Nov 22 '18 at 12:29
It is noteworthy that we cannot refer to evaluated expression aliases in the
ONclause ofJOIN.– Madhur Bhaiya
Nov 22 '18 at 12:57