NinjaTaktikz

SELECT SUM(IF(increment_day < '2010-06-01',SUM(vdat_2992.counts),SUM(vdat_2449.clicktositeindicator))) as CTS FROM ( SELECT * FROM vdat_2874 WHERE increment_day >= '2010-06-01' UNION SELECT * FROM vdat_2992 WHERE increment_day <= '2010-06-01' ) detail WHERE campaignid IN (1633605) I keep getting error saying Category Timestamp Duration Message Line Position Error 7/14/2011 12:00:44 PM 0:00:11.875 <link> - MySQL Database Error: Unknown column 'vdat_2992.counts' in 'field list' 1 0 What am I doing wrong. I even gave the database table in front of the column. Please Help. Thank You Gary

Hi Guys, Just joined today I was looking for help online could not find it so thought I would post here. mySql Version : 5.1.54-log Raw Sql Query : SELECT maintable.* FROM (SELECT details.* , vdat2309.demoflightcreativeid, CTS.Interactions , CTS.totalCTS , sessionCTS , Engagements , ImpressionRequest , Impression , avgtimespent FROM ( SELECT creativeid , opportunitylineitemid , concat(opportunitylineitemid, " : ",creativename) AS creativename , detail.flightcreativeid AS detailflightcreative, context , eventtype , concat(IFNULL(context,"null")," : ",id) AS elementname , SUM(counts) AS viewactions , SUM(q1) AS q1 , SUM(q2) AS q2 , SUM(q3) AS q3 , SUM(q4) AS q4 FROM ( SELECT * FROM vdat_2874 WHERE increment_day >= '2010-06-01' UNION SELECT * FROM vdat_2992 WHERE increment_day <= '2010-06-01' ) detail JOIN ad_dim header ON header.flightcreativeid = detail.flightcreativeid WHERE header.campaignid IN (1633605) AND detail.increment_day <= "2011-07-14" AND header.billingmetric LIKE '%CPE%' AND header.productname LIKE '%adframes%' AND header.flighttype = "Paid" GROUP BY context , eventtype , id , creativeid , creativename, opportunitylineitemid ) AS details LEFT OUTER JOIN ( SELECT summary.flightcreativeid AS summflightcreative, creativeid , creativename , opportunitylineitemid , SUM(engagement) AS Engagements , SUM(impression) AS Impression , SUM(impressionrequest) AS ImpressionRequest , (SUM(ats*atscount)/SUM(atscount))/1000 AS avgtimespent , SUM(clicktositeindicator) AS sessionCTS FROM adv_summary summary JOIN ad_dim dim ON summary.flightcreativeid = dim.flightcreativeid WHERE dim.campaignid IN (1633605) AND summary.increment_day <= "2011-07-14" AND dim.productname LIKE 'adframes%' AND dim.billingmetric LIKE '%CPE%' AND dim.flighttype LIKE "%Paid" GROUP BY creativeid, opportunitylineitemid ) AS summ ON summ.summflightcreative = details.detailflightcreative AND summ.opportunitylineitemid = details.opportunitylineitemid LEFT OUTER JOIN ( SELECT SUM(IF(eventtype LIKE 'cta' , counts, 0)) AS totalCTS , SUM(IF(eventtype NOT LIKE 'cta' , counts, 0)) AS Interactions, creativeid , opportunitylineitemid FROM ( SELECT * FROM vdat_2874 WHERE increment_day >= '2010-06-01' UNION SELECT * FROM vdat_2992 WHERE increment_day <= '2010-06-01' ) detail LEFT JOIN ad_dim dim ON dim.flightcreativeid = detail.flightcreativeid WHERE campaignid IN (1633605) AND detail.increment_day <= "2011-07-14" AND dim.productname LIKE '%adframes%' AND dim.billingmetric LIKE '%CPE%' AND dim.flighttype LIKE 'Paid' GROUP BY opportunitylineitemid, creativeid ) AS CTS ON CTS.creativeid = summ.creativeid AND CTS.opportunitylineitemid = summ.opportunitylineitemid LEFT JOIN (SELECT demoflightcreativeid, creativeid FROM vdat_2309 GROUP BY creativeid ) vdat2309 ON vdat2309.creativeid = summ.creativeid ) AS maintable ORDER BY opportunitylineitemid, creativeid Here is the situation. I been assigned to project where I work with Pentaho Reporting. The queries are written in mySQL. Basic Info : 2 Tables adv_detail, adv_summary New Tables : vdat_2992, vdat_2874 Objective : Create a Migration date to pull data from 2 different tables vdat_2992 and vdat_2874. Data : Data I am suppose to pull is CTS (Clicks to Site) What I have done so far: I made a union with vdat_2992 and vdat_2874 since both of them have extacly same columns. ( SELECT * FROM vdat_2874 WHERE increment_day >= '2010-06-01' UNION SELECT * FROM vdat_2992 WHERE increment_day <= '2010-06-01' ) Problem : Usually the data is pulled from adv_summary(vdat_2449) table and we have a column called impressions. So what we do is get the totalCTS and divide that by impressions to get the CTSR(Click to site rate). Now the objective is to get the CTSR but this time it has to pull CTS from vdat_2874 before 2010-06-01 and then pull the CTS from vdat_2992 after 2010-06-01 then add both of them together to get the CTS rate and dived that by impressions to get the CTSR. Now this is the code that I came up with to get the CTS SUM ( IF ( INCREMENT_DAY < 'MigrationDate' ,SUM(vdat_2992.counts where eventtype="cta") ,SUM(vdat_2449.clicktositeindicator) ) ) as totalCTS counts = CTS(Click to sites) clicktositeindicator = Also CTS just named differently. I keep getting errors that tables are not found but they are all in the database. Once I get the data and the totalCTS is complete I will need to go into Pentaho and use the formula there to get the totalCTS. Any help is appreciated. If you need more info please ask. I know this is little bit confusing. I am really confused to. Thank You Gary