# 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?

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, ...


Answered by Rick James on October 29, 2020