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
