0

I have a table that records events regarding email campaigns. I want to figure out the percentage of campaigns where there was more than one event happening for the campaign.

First I calculated the number of events happening in each campaign:

select count(*) as counter
               from campaigns_log
               where event IN ('send', 'open')
                 and campaign_id is not null
               group by campaign_id, email

Then I grouped the campaigns in the condition whether more than one campaign happened:

select count(counter) as occurences, IF(counter > 1, 2, 1) as grouper
         from (select count(*) as counter
               from campaigns_log
               where event IN ('send', 'open')
                 and campaign_id is not null
               group by campaign_id, email) as counters_table
         group by grouper

Sample result:

occurences ¦ grouper
132        ¦ 1
360        ¦ 2

Now I want to calculate for each row the percentage of total occurrences. So something like this:

occurences ¦ grouper ¦ percentage
132        ¦ 1       ¦ 132/(132+360)
360        ¦ 2       ¦ 360/(132+360)

I tried this, but it does not work, it does not properly calculate the sum total:

select *, occurences/(select sum(occurences))
from (
         select count(counter) as occurences, IF(counter > 1, 2, 1) as grouper
         from (select count(*) as counter
               from campaigns_log
               where event IN ('send', 'open')
                 and campaign_id is not null
               group by campaign_id, email) as counters_table
         group by grouper
     ) as occurences_table group by occurences, grouper

Any idea where is my mistake in the last step?

  • @a_horse_with_no_name Sorry, wrong tag, but the sentiment is the same. It does not really matter. It does for me but doesn't do what I need, so it does not matter. I need the last query that will produce the result I am looking for and described in the question. – Patrick Kusebauch Nov 8 at 14:18
  • @a_horse_with_no_name I have updated the tag appropriately. – Patrick Kusebauch Nov 8 at 14:21
  • Create a dbfiddle.uk/?rdbms=mysql_5.6 or similar with sample data that can be used to verify – Lennart Nov 8 at 14:25
  • What version of MySQL are you using? Please add the corresponding version-specific tag to your question. – Andriy M Nov 8 at 15:05
0

Part of the secret is

SELECT  (...)/total, ...
    FROM campaigns_log
    JOIN ( SELECT SUM(...) AS total FROM ... ) x

That is, compute the total separately and make it available in the expressions.

Similarly:

SELECT @total := SUM(...) FROM ...
SELECT (...)/@total, ...
  • Yeah, but the problem with your solution is that the occurences_table is not available in the JOIN expression, so I cannot calculate the total separately. – Patrick Kusebauch Nov 9 at 7:14
  • The same with the second solution I cannot assign to the variable select from a table that does not exist, but in only in time calculated as a part of the sub query. – Patrick Kusebauch Nov 9 at 7:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.