MySQL Forums
Forum List  »  Newbie

MySQL query and sub query
Posted by: Dev Chauhan
Date: May 17, 2018 08:37AM

Hello

I have below 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`

(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"))

Where it gives me error as below :

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

If you see the above query it has sub query as below

(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

I believe that is not properly rendered could anyone please help me on how this subquery used as select statement in first part of the query STNegData1W.query and bottom part of the query

Options: ReplyQuote


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


Sorry, only registered users may post in this forum.

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.