MySQL Forums
Forum List  »  Newbie

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

The subquery eliciting the error ...

   STNegData4W.metricId, ut   STNegData4W.keywordId, 
 FROM ...

is in the Select list, ie it's "correlated", and it's still malformed in two ways, (i) it specifies four columns, but one correlated subquery in the select list is allowed to specify exactly one column, and (ii) there needs to be a comma before it.

And remember, a correlated subquery in the Select list needs to be fully evaluated for every row found by the outer query. It will perform like a pig. The correlated subquery should be moved to the FROM clause, creating a query looking something like this ...

SELECT AS `Campaign Name`, AS `Adgroup Name`, 
  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
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 = 
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 = 
     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 = 
   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 = ...                       -- JUST MY GUESS CORRECT IF NEED BE
GROUP BY,, STNegData1W.query, keywords_5.matchType, 
         metrics_5.sales, campaigns_5.user_market_id, Right(,4) 
HAVING Sum(metrics_5.clicks))>5 
   AND campaigns_5.user_market_id=12 
   AND Right(,4)="PFUS";

... though since only you have the query spec and the tables, you'll undoubtedly need to touch it up. And the move of the subquery to the From clause will probably make some simplifications possible too.

Edited 2 time(s). Last edit at 05/17/2018 09:48AM by Peter Brawley.

Options: ReplyQuote

Written By
May 17, 2018 08:37AM
Re: MySQL query and sub query
May 17, 2018 09:43AM

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.