[Maria-discuss] MySQL Quries and Error
Below is my queries SELECT AdData4w.`Campaign Name`, AdData4w.`Adgroup Name`, AdData4w.`4w Clicks`, AdData1w.`1w Clicks`, AdData4w.`4w Orders`, AdData1w.`1w Orders`, AdData4w.`4w Spend`, AdData1w.`1w Spend`, AdData4w.`4w Sales`, AdData1w.`1w Sales`, AdData4w.`4w Conv Rate`, AdData1w.`1w Conv Rate`, AdData4w.`4w ACOS`, AdData1w.`1w ACOS`, AdData4w.`4w CPC`, AdData1w.`1w CPC`,AdData4w.`4w Bid`, AdData1w.`1w Bid` FROM AdData4w INNER JOIN AdData1w ON (AdData4w.`Adgroup Name` = AdData1w.`Adgroup Name`) AND (AdData4w.`Campaign Name` = AdData1w.`Campaign Name`) GROUP BY AdData4w.`Campaign Name`, AdData4w.`Adgroup Name`, AdData4w.`4w Clicks`, AdData1w.`1w Clicks`, AdData4w.`4w Orders`, AdData1w.`1w Orders`, AdData4w.`4w Spend`, AdData1w.`1w Spend`, AdData4w.`4w Sales`, AdData1w.`1w Sales`, AdData4w.`4w Conv Rate`, AdData1w.`1w Conv Rate`, AdData4w.`4w ACOS`, AdData1w.`1w ACOS`, AdData4w.`4w CPC`, AdData1w.`1w CPC`, AdData4w.`4w Bid`, AdData1w.`1w Bid` From (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, Sum(metrics_5.clicks) AS `1w Clicks`, Sum(metrics_5.orders) AS `1w Orders`, Sum(metrics_5.costs) AS `1w Spend`, Sum(metrics_5.sales) AS `1w Sales`, If(Sum(clicks)=0,0,Sum(orders)/Sum(clicks)) AS `1w Conv Rate`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `1w ACOS`, IF (Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `1w CPC`, Avg(bid_info_5.value) AS `1w Bid` FROM ((adgroups_5 INNER JOIN campaigns_5 ON adgroups_5.campaignId = campaigns_5.campaignId) INNER JOIN metrics_5 ON adgroups_5.adGroupId = metrics_5.type_id) INNER JOIN bid_info_5 ON adgroups_5.adGroupId = bid_info_5.type_id WHERE (((metrics_5.created_at)>'5/10/2018'-7)) GROUP BY campaigns_5.name, adgroups_5.name, campaigns_5.user_market_id, RIGHT(campaigns_5.name,4) HAVING (((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name,4))="PFUS") AND ((Sum(metrics_5.impressions))>0)) ORDER BY campaigns_5.name, adgroups_5.name) AS AdData1w INNER JOIN (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, Sum(metrics_5.clicks) AS `4w Clicks`, Sum(metrics_5.orders) AS `4w Orders`, Sum(metrics_5.costs) AS `4w Spend`, Sum(metrics_5.sales) AS `4w Sales`, If(Sum(clicks)=0,0,Sum(orders)/Sum(clicks)) AS `4w Conv Rate`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `4w ACOS`, If(Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `4w CPC`, Avg(bid_info_5.value) AS `4w Bid` FROM ((adgroups_5 INNER JOIN campaigns_5 ON adgroups_5.campaignId = campaigns_5.campaignId) INNER JOIN metrics_5 ON adgroups_5.adGroupId = metrics_5.type_id) INNER JOIN bid_info_5 ON adgroups_5.adGroupId = bid_info_5.type_id WHERE (((metrics_5.created_at)>'5/10/2018'-28)) GROUP BY campaigns_5.name, adgroups_5.name, campaigns_5.user_market_id, RIGHT(campaigns_5.name,4) HAVING (((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name,4))="PFUS") AND ((Sum(metrics_5.impressions))>0)) ORDER BY campaigns_5.name, adgroups_5.name) AS AdData4w it gives error on as below 4 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 'From (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup N' at line 4 Query12.sql 1 1 Please assist.
Incorrect order of a few lines. This parses successfully… SELECT AdData4w.`Campaign Name`, AdData4w.`Adgroup Name`, AdData4w.`4w Clicks`, AdData1w.`1w Clicks`, AdData4w.`4w Orders`, AdData1w.`1w Orders`, AdData4w.`4w Spend`, AdData1w.`1w Spend`, AdData4w.`4w Sales`, AdData1w.`1w Sales`, AdData4w.`4w Conv Rate`, AdData1w.`1w Conv Rate`, AdData4w.`4w ACOS`, AdData1w.`1w ACOS`, AdData4w.`4w CPC`, AdData1w.`1w CPC`,AdData4w.`4w Bid`, AdData1w.`1w Bid` FROM AdData4w INNER JOIN (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, SUM(metrics_5.clicks) AS `1w Clicks`, SUM(metrics_5.orders) AS `1w Orders`, SUM(metrics_5.costs) AS `1w Spend`, SUM(metrics_5.sales) AS `1w Sales`, IF(SUM(clicks)=0,0,SUM(orders)/SUM(clicks)) AS `1w Conv Rate`, IF(SUM(metrics_5.sales)=0,0,SUM(metrics_5.costs)/SUM(metrics_5.sales)) AS `1w ACOS`, IF (SUM(clicks)=0,0,SUM(costs)/SUM(clicks)) AS `1w CPC`, AVG(bid_info_5.value) AS `1w Bid` FROM ((adgroups_5 INNER JOIN campaigns_5 ON adgroups_5.campaignId = campaigns_5.campaignId) INNER JOIN metrics_5 ON adgroups_5.adGroupId = metrics_5.type_id) INNER JOIN bid_info_5 ON adgroups_5.adGroupId = bid_info_5.type_id WHERE (((metrics_5.created_at)>'5/10/2018'-7)) GROUP BY campaigns_5.name, adgroups_5.name, campaigns_5.user_market_id, RIGHT(campaigns_5.name,4) HAVING (((campaigns_5.user_market_id)=12) AND ((RIGHT(campaigns_5.name,4))="PFUS") AND ((SUM(metrics_5.impressions))>0)) ORDER BY campaigns_5.name, adgroups_5.name) AS AdData1w INNER JOIN (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, SUM(metrics_5.clicks) AS `4w Clicks`, SUM(metrics_5.orders) AS `4w Orders`, SUM(metrics_5.costs) AS `4w Spend`, SUM(metrics_5.sales) AS `4w Sales`, IF(SUM(clicks)=0,0,SUM(orders)/SUM(clicks)) AS `4w Conv Rate`, IF(SUM(metrics_5.sales)=0,0,SUM(metrics_5.costs)/SUM(metrics_5.sales)) AS `4w ACOS`, IF(SUM(clicks)=0,0,SUM(costs)/SUM(clicks)) AS `4w CPC`, AVG(bid_info_5.value) AS `4w Bid` FROM ((adgroups_5 INNER JOIN campaigns_5 ON adgroups_5.campaignId = campaigns_5.campaignId) INNER JOIN metrics_5 ON adgroups_5.adGroupId = metrics_5.type_id) INNER JOIN bid_info_5 ON adgroups_5.adGroupId = bid_info_5.type_id WHERE (((metrics_5.created_at)>'5/10/2018'-28)) GROUP BY campaigns_5.name, adgroups_5.name, campaigns_5.user_market_id, RIGHT(campaigns_5.name,4) HAVING (((campaigns_5.user_market_id)=12) AND ((RIGHT(campaigns_5.name,4))="PFUS") AND ((SUM(metrics_5.impressions))>0)) ORDER BY campaigns_5.name, adgroups_5.name) AS AdData4w INNER JOIN AdData1w ON (AdData4w.`Adgroup Name` = AdData1w.`Adgroup Name`) AND (AdData4w.`Campaign Name` = AdData1w.`Campaign Name`) GROUP BY AdData4w.`Campaign Name`, AdData4w.`Adgroup Name`, AdData4w.`4w Clicks`, AdData1w.`1w Clicks`, AdData4w.`4w Orders`, AdData1w.`1w Orders`, AdData4w.`4w Spend`, AdData1w.`1w Spend`, AdData4w.`4w Sales`, AdData1w.`1w Sales`, AdData4w.`4w Conv Rate`, AdData1w.`1w Conv Rate`, AdData4w.`4w ACOS`, AdData1w.`1w ACOS`, AdData4w.`4w CPC`, AdData1w.`1w CPC`, AdData4w.`4w Bid`, AdData1w.`1w Bid` Obviously I don’t have the table structure and data to profile this but this look like this is going to be a killer query. Cheers, Rhys From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Dev C Sent: 16 May 2018 15:02 To: maria-discuss@lists.launchpad.net; Maria Developers <maria-developers@lists.launchpad.net> Subject: [Maria-discuss] MySQL Quries and Error Below is my queries SELECT AdData4w.`Campaign Name`, AdData4w.`Adgroup Name`, AdData4w.`4w Clicks`, AdData1w.`1w Clicks`, AdData4w.`4w Orders`, AdData1w.`1w Orders`, AdData4w.`4w Spend`, AdData1w.`1w Spend`, AdData4w.`4w Sales`, AdData1w.`1w Sales`, AdData4w.`4w Conv Rate`, AdData1w.`1w Conv Rate`, AdData4w.`4w ACOS`, AdData1w.`1w ACOS`, AdData4w.`4w CPC`, AdData1w.`1w CPC`,AdData4w.`4w Bid`, AdData1w.`1w Bid` FROM AdData4w INNER JOIN AdData1w ON (AdData4w.`Adgroup Name` = AdData1w.`Adgroup Name`) AND (AdData4w.`Campaign Name` = AdData1w.`Campaign Name`) GROUP BY AdData4w.`Campaign Name`, AdData4w.`Adgroup Name`, AdData4w.`4w Clicks`, AdData1w.`1w Clicks`, AdData4w.`4w Orders`, AdData1w.`1w Orders`, AdData4w.`4w Spend`, AdData1w.`1w Spend`, AdData4w.`4w Sales`, AdData1w.`1w Sales`, AdData4w.`4w Conv Rate`, AdData1w.`1w Conv Rate`, AdData4w.`4w ACOS`, AdData1w.`1w ACOS`, AdData4w.`4w CPC`, AdData1w.`1w CPC`, AdData4w.`4w Bid`, AdData1w.`1w Bid` From (SELECT campaigns_5.name<http://campaigns_5.name> AS `Campaign Name`, adgroups_5.name<http://adgroups_5.name> AS `Adgroup Name`, Sum(metrics_5.clicks) AS `1w Clicks`, Sum(metrics_5.orders) AS `1w Orders`, Sum(metrics_5.costs) AS `1w Spend`, Sum(metrics_5.sales) AS `1w Sales`, If(Sum(clicks)=0,0,Sum(orders)/Sum(clicks)) AS `1w Conv Rate`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `1w ACOS`, IF (Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `1w CPC`, Avg(bid_info_5.value) AS `1w Bid` FROM ((adgroups_5 INNER JOIN campaigns_5 ON adgroups_5.campaignId = campaigns_5.campaignId) INNER JOIN metrics_5 ON adgroups_5.adGroupId = metrics_5.type_id) INNER JOIN bid_info_5 ON adgroups_5.adGroupId = bid_info_5.type_id WHERE (((metrics_5.created_at)>'5/10/2018'-7)) GROUP BY campaigns_5.name<http://campaigns_5.name>, adgroups_5.name<http://adgroups_5.name>, campaigns_5.user_market_id, RIGHT(campaigns_5.name<http://campaigns_5.name>,4) HAVING (((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name<http://campaigns_5.name>,4))="PFUS") AND ((Sum(metrics_5.impressions))>0)) ORDER BY campaigns_5.name<http://campaigns_5.name>, adgroups_5.name<http://adgroups_5.name>) AS AdData1w INNER JOIN (SELECT campaigns_5.name<http://campaigns_5.name> AS `Campaign Name`, adgroups_5.name<http://adgroups_5.name> AS `Adgroup Name`, Sum(metrics_5.clicks) AS `4w Clicks`, Sum(metrics_5.orders) AS `4w Orders`, Sum(metrics_5.costs) AS `4w Spend`, Sum(metrics_5.sales) AS `4w Sales`, If(Sum(clicks)=0,0,Sum(orders)/Sum(clicks)) AS `4w Conv Rate`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `4w ACOS`, If(Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `4w CPC`, Avg(bid_info_5.value) AS `4w Bid` FROM ((adgroups_5 INNER JOIN campaigns_5 ON adgroups_5.campaignId = campaigns_5.campaignId) INNER JOIN metrics_5 ON adgroups_5.adGroupId = metrics_5.type_id) INNER JOIN bid_info_5 ON adgroups_5.adGroupId = bid_info_5.type_id WHERE (((metrics_5.created_at)>'5/10/2018'-28)) GROUP BY campaigns_5.name<http://campaigns_5.name>, adgroups_5.name<http://adgroups_5.name>, campaigns_5.user_market_id, RIGHT(campaigns_5.name<http://campaigns_5.name>,4) HAVING (((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name<http://campaigns_5.name>,4))="PFUS") AND ((Sum(metrics_5.impressions))>0)) ORDER BY campaigns_5.name<http://campaigns_5.name>, adgroups_5.name<http://adgroups_5.name>) AS AdData4w it gives error on as below 4 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 'From (SELECT campaigns_5.name<http://campaigns_5.name> AS `Campaign Name`, adgroups_5.name<http://adgroups_5.name> AS `Adgroup N' at line 4 Query12.sql 1 1 Please assist.
participants (2)
-
Dev C -
Rhys.Campbell＠swisscom.com