MySQL Forums
Forum List  »  Newbie

Re: MySQL query and sub query
Posted by: Dev Chauhan
Date: May 17, 2018 10:44AM

Thanks Peter, after your suggestion below is the query

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`,
STNegData4W.query,
STNegData4W.metricId,
STNegData4W.keywordId,
metrics_5.impressions
FROM
((((keywords_5 INNER JOIN campaigns_5 ON keywords_5.campaignId = campaigns_5.campaignId)
JOIN adgroups_5 ON keywords_5.adGroupId = adgroups_5.adGroupId)
JOIN STNegData1W ON keywords_5.keywordId = x.keywordId)
JOIN metrics_5 ON x.metricId = metrics_5.id
JOIN ( -- CORRELATED QUERY MOVED HERE
SELECT query, metricId, keywordId, Impressions
FROM (
SELECT searchterm_5.query, searchterm_5.metricId,
searchterm_5.keywordId, Sum(metrics_5.orders) AS `4W Orders`
FROM searchterm_5
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
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 X JOIN metrics_5 ON x.metricId = metrics_5.id -- JUST MY GUESS CORRECT IF NEED BE
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"

It gives error as below

12 Not unique table/alias: 'metrics_5' Query1.sql 20 7

Options: ReplyQuote


Subject
Written By
Posted
May 17, 2018 08:37AM
Re: MySQL query and sub query
May 17, 2018 10:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.