Database Administrators Asked by Patrick Kusebauch on October 29, 2020
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
1 Asked on February 4, 2021
2 Asked on February 3, 2021 by leona
3 Asked on February 2, 2021 by emanuele-paolini
1 Asked on January 31, 2021 by miguel-ramires
1 Asked on January 30, 2021 by milkncookiez
2 Asked on January 29, 2021 by chris-jenner
2 Asked on January 28, 2021 by stalinko
1 Asked on January 28, 2021 by thedemonlord
1 Asked on January 26, 2021 by elephantcoder
1 Asked on January 25, 2021 by rdrgtec
2 Asked on January 25, 2021 by dstr
2 Asked on January 24, 2021 by jac
1 Asked on January 23, 2021 by lloyd-thomas
1 Asked on January 18, 2021 by narendra
1 Asked on January 14, 2021 by din
1 Asked on January 14, 2021 by vince-kronlein
2 Asked on January 14, 2021 by nico-m
3 Asked on January 13, 2021 by roy-hinkley
Get help from others!
Recent Answers
Recent Questions
© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP