i've got a table campaign that has particulars of campaign mails sent.
campaign_table: campaign_id campaign_name flag 1 test1 1 2 test2 1 3 test3 0
another table campaign activity that has particulars of campaign activities.
campaign_activity: campaign_id is_clicked is_opened 1 0 1 1 1 0 2 0 1 2 1 0
I wish to get all campaigns with flag value 3 and the amount of is_clicked on posts with value 1 and quantity of posts with is_opened up value 1 in one query.
ie. campaign_id campaign_name numberofclicks numberofopens 1 test1 1 1 2 test2 1 1
Used to do this using sub-query using the query:
select c.campaign_id,c.campaign_name, (SELECT count(campaign_id) from campaign_activity WHERE campaign_id=c.id AND is_clicked=1) as numberofclicks, (SELECT count(campaign_id) from campaign_activity WHERE campaign_id=c.id AND is_clicked=1) as numberofopens FROM campaign c WHERE c.flag=1
But people state that using sub-queries are a bad coding convention and make use of join rather than sub-queries. But i'm not sure ways to get exactly the same result using join. I consulted with a few of my co-workers and they're stating that it is not easy to use participate in this case. Can you really obtain the same result using joins? if so, please let me know how.
is_opened are just ever 1 or , this will work:
select c.campaign_id, c.campaign_name, sum(d.is_clicked), sum(d.is_opened) from campaign c inner join campaign_activity d on c.campaign_id = d.campaign_id where c.flag = 1 group by c.campaign_id, c.campaign_name
This will have the desired effect. Substitute INNER JOIN for LEFT OUTER JOIN if you wish to include campaigns without any activity.
SELECT c.Campaign_ID , c.Campaign_Name , SUM(CASE WHEN a.Is_Clicked = 1 THEN 1 ELSE 0 END) AS NumberOfClicks , SUM(CASE WHEN a.Is_Opened = 1 THEN 1 ELSE 0 END) AS NumberOfOpens FROM dbo.Campaign c INNER JOIN dbo.Campaign_Activity a ON a.Campaign_ID = c.Campaign_ID GROUP BY c.Campaign_ID , c.Campaign_Name
Hmm. Is what you would like as easy as this? I am unsure I am reading through the question right...
SELECT campaign_table.campaign_id, SUM(is_clicked), SUM(is_opened) FROM campaign_table INNER JOIN campaign_activity ON campaign_table.campaign_id = campaign_activity.campaign_id WHERE campaign_table.flag = 1 GROUP BY campaign_table.campaign_id
Observe that by having an INNER JOIN here, you will not see campaigns where there is nothing corresponding within the campaign_activity table. For the reason that circumstance, you need to use a LEFT JOIN, and convert NULL to within the SUM, e.g. SUM(IFNULL(is_clicked on, )).
I guess this will get it done :
select * from campaign_table inner join campaign_activity on campaign_table.id = campaign_activity.id where campaign_table.flag = 3 and campaign_activity.is_clicked = 1 and campaign_activity.is_opened = 1
Attn : this isn't examined inside a live situation
The SQL in it's easiest form and many robust form is: (formatted for readability)
SELECT campaign_table.campaign_ID, campaign_table.campaign_name, Sum(campaign_activity.is_clicked) AS numberofclicks, Sum(campaign_activity.is_open) AS numberofopens FROM campaign_table INNER JOIN campaign_activity ON campaign_table.campaign_ID = campaign_activity.campaign_ID GROUP BY campaign_table.campaign_ID, campaign_table.campaign_name, campaign_table.flag HAVING campaign_table.flag=1;