The subquery eliciting the error ...
(SELECT
STNegData4W.query,
STNegData4W.metricId, ut STNegData4W.keywordId,
metrics_5.impressions
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
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";
... 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.