maria-discuss team mailing list archive

[Dev C <chauhan.devsur@xxxxxxxxx>]

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@xxxxxxxxx> 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@xxxxxxxxx>
> 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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx] *On Behalf Of *Dev C
>>> *Sent:* 17 May 2018 06:41
>>> *To:* maria-discuss@xxxxxxxxxxxxxxxxxxx; Maria Developers <
>>> maria-developers@xxxxxxxxxxxxxxxxxxx>
>>> *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@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>> More help   : https://help.launchpad.net/ListHelp
>>>
>>>
>>
>