[Maria-discuss] Query ambiguity
Hello I have query ambiguity as attached. Please help me on how to solve that.
You're not making a lot of sense here. Please explain the "ambiguity". That's a fairly complex query so you should include as text. "Does not work" <- In the image. It helps if you include error message. From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Dev C Sent: 17 May 2018 06:41 To: maria-discuss@lists.launchpad.net; Maria Developers <maria-developers@lists.launchpad.net> Subject: [Maria-discuss] Query ambiguity Hello I have query ambiguity as attached. Please help me on how to solve that. [cid:image001.png@01D3EDD9.EBA2D680]
What Rhys said, please post queries as text, not as images. The issue is likely due to the way you structured your query. You have put a derived table in a random place (in the SELECT list). Please try to read about CTEs. It will help you design those complex queries better and make them more readable and easy to edit: https://mariadb.com/kb/en/library/with/ Best regards Pantelis Theodosiou On Thu, May 17, 2018 at 11:23 AM, <Rhys.Campbell@swisscom.com> wrote:
You're not making a lot of sense here. Please explain the "ambiguity". That's a fairly complex query so you should include as text.
"Does not work" <- In the image. It helps if you include error message.
*From:* Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell= swisscom.com@lists.launchpad.net] *On Behalf Of *Dev C *Sent:* 17 May 2018 06:41 *To:* maria-discuss@lists.launchpad.net; Maria Developers < maria-developers@lists.launchpad.net> *Subject:* [Maria-discuss] Query ambiguity
Hello
I have query ambiguity as attached. Please help me on how to solve that.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
I think the OP needs to try a lot of googling and stackoverflow etc first and break the heads first and then post it here if it's really required. IMHO, if people start asking the SQL problems (mainly most of the sql issues are knowledge gap related) here, then I think the quality of this forum will be compromised. On Thu, May 17, 2018, 6:36 AM Pantelis Theodosiou <ypercube@gmail.com> wrote:
What Rhys said, please post queries as text, not as images.
The issue is likely due to the way you structured your query. You have put a derived table in a random place (in the SELECT list).
Please try to read about CTEs. It will help you design those complex queries better and make them more readable and easy to edit: https://mariadb.com/kb/en/library/with/
Best regards
Pantelis Theodosiou
On Thu, May 17, 2018 at 11:23 AM, <Rhys.Campbell@swisscom.com> wrote:
You're not making a lot of sense here. Please explain the "ambiguity". That's a fairly complex query so you should include as text.
"Does not work" <- In the image. It helps if you include error message.
*From:* Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell= swisscom.com@lists.launchpad.net] *On Behalf Of *Dev C *Sent:* 17 May 2018 06:41 *To:* maria-discuss@lists.launchpad.net; Maria Developers < maria-developers@lists.launchpad.net> *Subject:* [Maria-discuss] Query ambiguity
Hello
I have query ambiguity as attached. Please help me on how to solve that.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Here is the full code and picture on what is required. SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, STNegData1W.query, keywords_5.matchType AS `Match Type`, Sum(metrics_5.clicks) AS `LT Clicks`, Sum(metrics_5.costs) AS `LT Spend`, Sum(metrics_5.orders) AS `LT Orders`, metrics_5.sales AS `LT Sales`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `LT ACOS`, If(Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `LT CPC` FROM (((keywords_5 INNER JOIN campaigns_5 ON keywords_5.campaignId = campaigns_5.campaignId) INNER JOIN adgroups_5 ON keywords_5.adGroupId = adgroups_5.adGroupId) INNER JOIN STNegData1W ON keywords_5.keywordId = STNegData1W.keywordId) INNER JOIN metrics_5 ON STNegData1W.metricId = metrics_5.id, (SELECT STNegData4W.query, STNegData4W.metricId, STNegData4W.keywordId, metrics_5.impressions FROM (SELECT searchterm_5.query, searchterm_5.metricId, searchterm_5.keywordId, Sum(metrics_5.orders) AS `4W Orders` FROM searchterm_5 INNER JOIN metrics_5 ON searchterm_5.metricId = metrics_5.id GROUP BY searchterm_5.query, searchterm_5.metricId, searchterm_5.keywordId, metrics_5.created_at HAVING (((Sum(metrics_5.orders))=0) AND ((metrics_5.created_at) > DATE_SUB('2018-05-10',INTERVAL 28 DAY)))) AS STNegData4W INNER JOIN metrics_5 ON STNegData4W.metricId = metrics_5.id GROUP BY STNegData4W.query, STNegData4W.metricId, STNegData4W.keywordId, metrics_5.impressions, metrics_5.created_at HAVING (((metrics_5.impressions)>0) AND ((metrics_5.created_at)> DATE_SUB('2018-05-10', INTERVAL 7 DAY)))) AS STNegData1W GROUP BY campaigns_5.name, adgroups_5.name, STNegData1W.query, keywords_5.matchType, metrics_5.sales, campaigns_5.user_market_id, Right( campaigns_5.name,4) HAVING (((Sum(metrics_5.clicks))>5) AND ((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name,4))="PFUS")) On Thu, May 17, 2018 at 4:06 PM, Pantelis Theodosiou <ypercube@gmail.com> wrote:
What Rhys said, please post queries as text, not as images.
The issue is likely due to the way you structured your query. You have put a derived table in a random place (in the SELECT list).
Please try to read about CTEs. It will help you design those complex queries better and make them more readable and easy to edit: https://mariadb.com/kb/en/library/with/
Best regards
Pantelis Theodosiou
On Thu, May 17, 2018 at 11:23 AM, <Rhys.Campbell@swisscom.com> wrote:
You're not making a lot of sense here. Please explain the "ambiguity". That's a fairly complex query so you should include as text.
"Does not work" <- In the image. It helps if you include error message.
*From:* Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell= swisscom.com@lists.launchpad.net] *On Behalf Of *Dev C *Sent:* 17 May 2018 06:41 *To:* maria-discuss@lists.launchpad.net; Maria Developers < maria-developers@lists.launchpad.net> *Subject:* [Maria-discuss] Query ambiguity
Hello
I have query ambiguity as attached. Please help me on how to solve that.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Modified code and error *** SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, STNegData1W.query, keywords_5.matchType AS `Match Type`, Sum(metrics_5.clicks) AS `LT Clicks`, Sum(metrics_5.costs) AS `LT Spend`, Sum(metrics_5.orders) AS `LT Orders`, metrics_5.sales AS `LT Sales`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `LT ACOS`, If(Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `LT CPC` (SELECT STNegData4W.query, STNegData4W.metricId, STNegData4W.keywordId, metrics_5.impressions FROM (SELECT searchterm_5.query, searchterm_5.metricId, searchterm_5.keywordId, Sum(metrics_5.orders) AS `4W Orders` FROM searchterm_5 INNER JOIN metrics_5 ON searchterm_5.metricId = metrics_5.id GROUP BY searchterm_5.query, searchterm_5.metricId, searchterm_5.keywordId, metrics_5.created_at HAVING (((Sum(metrics_5.orders))=0) AND ((metrics_5.created_at) > DATE_SUB('2018-05-10',INTERVAL 28 DAY)))) AS STNegData4W INNER JOIN metrics_5 ON STNegData4W.metricId = metrics_5.id GROUP BY STNegData4W.query, STNegData4W.metricId, STNegData4W.keywordId, metrics_5.impressions, metrics_5.created_at HAVING (((metrics_5.impressions)>0) AND ((metrics_5.created_at)> DATE_SUB('2018-05-10', INTERVAL 7 DAY)))) AS STNegData1W FROM (((keywords_5 INNER JOIN campaigns_5 ON keywords_5.campaignId = campaigns_5.campaignId) INNER JOIN adgroups_5 ON keywords_5.adGroupId = adgroups_5.adGroupId) INNER JOIN STNegData1W ON keywords_5.keywordId = STNegData1W.keywordId) INNER JOIN metrics_5 ON STNegData1W.metricId = metrics_5.id GROUP BY campaigns_5.name, adgroups_5.name, STNegData1W.query, keywords_5.matchType, metrics_5.sales, campaigns_5.user_market_id, Right( campaigns_5.name,4) HAVING (((Sum(metrics_5.clicks))>5) AND ((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name,4))="PFUS")) *** Error 3 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(SELECT STNegData4W.query, STNegData4W.metricId, STNegData4W.keywordI' at line 13 Query7.sql 1 2 On Thu, May 17, 2018 at 7:35 PM, Dev C <chauhan.devsur@gmail.com> wrote:
Here is the full code and picture on what is required.
SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, STNegData1W.query, keywords_5.matchType AS `Match Type`, Sum(metrics_5.clicks) AS `LT Clicks`, Sum(metrics_5.costs) AS `LT Spend`, Sum(metrics_5.orders) AS `LT Orders`, metrics_5.sales AS `LT Sales`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `LT ACOS`, If(Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `LT CPC` FROM (((keywords_5 INNER JOIN campaigns_5 ON keywords_5.campaignId = campaigns_5.campaignId) INNER JOIN adgroups_5 ON keywords_5.adGroupId = adgroups_5.adGroupId) INNER JOIN STNegData1W ON keywords_5.keywordId = STNegData1W.keywordId) INNER JOIN metrics_5 ON STNegData1W.metricId = metrics_5.id, (SELECT STNegData4W.query, STNegData4W.metricId, STNegData4W.keywordId, metrics_5.impressions FROM (SELECT searchterm_5.query, searchterm_5.metricId, searchterm_5.keywordId, Sum(metrics_5.orders) AS `4W Orders` FROM searchterm_5 INNER JOIN metrics_5 ON searchterm_5.metricId = metrics_5.id GROUP BY searchterm_5.query, searchterm_5.metricId, searchterm_5.keywordId, metrics_5.created_at HAVING (((Sum(metrics_5.orders))=0) AND ((metrics_5.created_at) > DATE_SUB('2018-05-10',INTERVAL 28 DAY)))) AS STNegData4W INNER JOIN metrics_5 ON STNegData4W.metricId = metrics_5.id GROUP BY STNegData4W.query, STNegData4W.metricId, STNegData4W.keywordId, metrics_5.impressions, metrics_5.created_at HAVING (((metrics_5.impressions)>0) AND ((metrics_5.created_at)> DATE_SUB('2018-05-10', INTERVAL 7 DAY)))) AS STNegData1W GROUP BY campaigns_5.name, adgroups_5.name, STNegData1W.query, keywords_5.matchType, metrics_5.sales, campaigns_5.user_market_id, Right( campaigns_5.name,4) HAVING (((Sum(metrics_5.clicks))>5) AND ((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name,4))="PFUS"))
On Thu, May 17, 2018 at 4:06 PM, Pantelis Theodosiou <ypercube@gmail.com> wrote:
What Rhys said, please post queries as text, not as images.
The issue is likely due to the way you structured your query. You have put a derived table in a random place (in the SELECT list).
Please try to read about CTEs. It will help you design those complex queries better and make them more readable and easy to edit: https://mariadb.com/kb/en/library/with/
Best regards
Pantelis Theodosiou
On Thu, May 17, 2018 at 11:23 AM, <Rhys.Campbell@swisscom.com> wrote:
You're not making a lot of sense here. Please explain the "ambiguity". That's a fairly complex query so you should include as text.
"Does not work" <- In the image. It helps if you include error message.
*From:* Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell= swisscom.com@lists.launchpad.net] *On Behalf Of *Dev C *Sent:* 17 May 2018 06:41 *To:* maria-discuss@lists.launchpad.net; Maria Developers < maria-developers@lists.launchpad.net> *Subject:* [Maria-discuss] Query ambiguity
Hello
I have query ambiguity as attached. Please help me on how to solve that.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Check whether you really need a comma here: *metrics_5.id <http://metrics_5.id/>,* *(SELECT * Also in your screen shot, AS 'LT CPC`, there is no comma and you use derived table in SELECT and trying to use JOIN in the FROM using this. Sorry to say this, I think you can either break this logic into small simple chunks and use SP to derive the logic in case you are not much comfortable writing a complex SQL. I think there is lot of syntax errors on it. On Thu, May 17, 2018 at 10:05 AM, Dev C <chauhan.devsur@gmail.com> wrote:
Here is the full code and picture on what is required.
SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, STNegData1W.query, keywords_5.matchType AS `Match Type`, Sum(metrics_5.clicks) AS `LT Clicks`, Sum(metrics_5.costs) AS `LT Spend`, Sum(metrics_5.orders) AS `LT Orders`, metrics_5.sales AS `LT Sales`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `LT ACOS`, If(Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `LT CPC` FROM (((keywords_5 INNER JOIN campaigns_5 ON keywords_5.campaignId = campaigns_5.campaignId) INNER JOIN adgroups_5 ON keywords_5.adGroupId = adgroups_5.adGroupId) INNER JOIN STNegData1W ON keywords_5.keywordId = STNegData1W.keywordId) INNER JOIN metrics_5 ON STNegData1W.metricId = *metrics_5.id <http://metrics_5.id>,* *(SELECT * STNegData4W.query, STNegData4W.metricId, STNegData4W.keywordId, metrics_5.impressions FROM (SELECT searchterm_5.query, searchterm_5.metricId, searchterm_5.keywordId, Sum(metrics_5.orders) AS `4W Orders` FROM searchterm_5 INNER JOIN metrics_5 ON searchterm_5.metricId = metrics_5.id GROUP BY searchterm_5.query, searchterm_5.metricId, searchterm_5.keywordId, metrics_5.created_at HAVING (((Sum(metrics_5.orders))=0) AND ((metrics_5.created_at) > DATE_SUB('2018-05-10',INTERVAL 28 DAY)))) AS STNegData4W INNER JOIN metrics_5 ON STNegData4W.metricId = metrics_5.id GROUP BY STNegData4W.query, STNegData4W.metricId, STNegData4W.keywordId, metrics_5.impressions, metrics_5.created_at HAVING (((metrics_5.impressions)>0) AND ((metrics_5.created_at)> DATE_SUB('2018-05-10', INTERVAL 7 DAY)))) AS STNegData1W GROUP BY campaigns_5.name, adgroups_5.name, STNegData1W.query, keywords_5.matchType, metrics_5.sales, campaigns_5.user_market_id, Right( campaigns_5.name,4) HAVING (((Sum(metrics_5.clicks))>5) AND ((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name,4))="PFUS"))
On Thu, May 17, 2018 at 4:06 PM, Pantelis Theodosiou <ypercube@gmail.com> wrote:
What Rhys said, please post queries as text, not as images.
The issue is likely due to the way you structured your query. You have put a derived table in a random place (in the SELECT list).
Please try to read about CTEs. It will help you design those complex queries better and make them more readable and easy to edit: https://mariadb.com/kb/en/library/with/
Best regards
Pantelis Theodosiou
On Thu, May 17, 2018 at 11:23 AM, <Rhys.Campbell@swisscom.com> wrote:
You're not making a lot of sense here. Please explain the "ambiguity". That's a fairly complex query so you should include as text.
"Does not work" <- In the image. It helps if you include error message.
*From:* Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell= swisscom.com@lists.launchpad.net] *On Behalf Of *Dev C *Sent:* 17 May 2018 06:41 *To:* maria-discuss@lists.launchpad.net; Maria Developers < maria-developers@lists.launchpad.net> *Subject:* [Maria-discuss] Query ambiguity
Hello
I have query ambiguity as attached. Please help me on how to solve that.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (4)
-
Dev C -
Karthick Subramanian -
Pantelis Theodosiou -
Rhys.Campbell＠swisscom.com