MySQL Forums
Forum List  »  Newbie

Re: MySQL query and sub query
Posted by: Peter Brawley
Date: May 17, 2018 10:55AM

Parenthesis problems ...

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,      -- MISSING COMMA
  X.query,                                                    -- 4 COLS FROM MOVED SUBQUERy
  X.metricId, 
  X.keywordId, 
  metrics_5.impressions 
FROM keywords_5 
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 = STNegData1W.keywordId) 
JOIN metrics_5   ON STNegData1W.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 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";

Again I don't have your tables so I can't really debug it.

Options: ReplyQuote


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


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.