AnswerBun.com

Calculating percentage over sub query SQL

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?

One Answer

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

Add your own answers!

Related Questions

ERROR: no schema has been selected to create in

3  Asked on February 2, 2021 by emanuele-paolini

     

ORACLE ASM present one or more LUNs?

1  Asked on January 31, 2021 by miguel-ramires

       

Order by certain enum values first

1  Asked on January 30, 2021 by milkncookiez

     

I have 600% high CPU usage mysqld

1  Asked on January 30, 2021 by alfredo

     

How to create a SELECT statement involving a subtype

1  Asked on January 26, 2021 by elephantcoder

       

How to move SSISDB database

2  Asked on January 24, 2021 by jac

       

Ident authentication failed for user “postgres”

1  Asked on January 23, 2021 by lloyd-thomas

       

Creacte Mysql Database copy

1  Asked on January 20, 2021 by viktor-mandrika

 

Fetching Remote Encryption Key for MariaDB

1  Asked on January 14, 2021 by vince-kronlein

   

Ask a Question

Get help from others!

© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP