# 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

## Related Questions

### Can I just restore the .diff file over full backup to speed up restore?

1  Asked on February 4, 2021

### Can connect using SQL Server Management Studio but cannot ping or via program

2  Asked on February 3, 2021 by leona

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

### I have 600% high CPU usage mysqld

1  Asked on January 30, 2021 by alfredo

### How to import multiple CSV files to a postgres table using pgadmin or other method?

2  Asked on January 29, 2021 by chris-jenner

### Counting Grouped records, using start date and end date with NULLs

1  Asked on January 28, 2021 by thedemonlord

### How to create a SELECT statement involving a subtype

1  Asked on January 26, 2021 by elephantcoder

### MySQL Installation: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’

1  Asked on January 25, 2021 by rdrgtec

### Limiting join to top 1 row for each row

2  Asked on January 25, 2021 by dstr

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

### What are minimum configurations for mongodb replica set though a java program

1  Asked on January 18, 2021 by narendra

### Solving intra parallel query deadlocks

1  Asked on January 14, 2021 by din

### Fetching Remote Encryption Key for MariaDB

1  Asked on January 14, 2021 by vince-kronlein

### A database differential backup without a full backup

2  Asked on January 14, 2021 by nico-m

### Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade

3  Asked on January 13, 2021 by roy-hinkley