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!

Rodrigo Roberto de Almeida
  • 93
  • 9

1 Answers1

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

P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • 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. - https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html – P.Salmon Nov 22 '18 at 13:21