# Calculating percentage over sub query SQL

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 '19 at 14:18
• @a_horse_with_no_name I have updated the tag appropriately. – Patrick Kusebauch Nov 8 '19 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 '19 at 14:25
• What version of MySQL are you using? Please add the corresponding version-specific tag to your question. – Andriy M Nov 8 '19 at 15:05

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 '19 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 '19 at 7:16