maria-discuss team mailing list archive

[<Rhys.Campbell@xxxxxxxxxxxx>]

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@xxxxxxxxxxxxxxxxxxx] On Behalf Of Dev C
Sent: 16 May 2018 15:02
To: maria-discuss@xxxxxxxxxxxxxxxxxxx; Maria Developers <maria-developers@xxxxxxxxxxxxxxxxxxx>
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.