Calculate percentage on an MySQL query

I have a query that returns some counts of data as follows, basically this table is posts and will contain either inbound or outbound posts. I want to make a query that will return the percentage of outbound posts.

e.g

select count(*) as total, 
(select count(*) from posts where inbound = 0 and accountid = 333) as inbound,
(select count(*) from posts where inbound = 1 and accountid = 333) as outbound 
from account a
    join posts p on p.accountId = a.id where a.id = 333 
group by a.id;

This returns something like the following…

+-------+---------+----------+
| total | inbound | outbound |
+-------+---------+----------+
|   802 |     525 |      277 |
+-------+---------+----------+

How would I modify the query above so it returns an additional column to calculate the outbound percent of the total, e.g (277 / 802) * 100 e.g (outbound / total) * 100)

So the expected output with the new column in the result would be as follows rounded off to the nearest whole number?

+-------+---------+----------+---------------------+
| total | inbound | outbound |  outboundPercentage |
+-------+---------+----------+---------------------+
|   802 |     525 |      277 |                  35 |
+-------+---------+----------+---------------------|

Answer

I think you can use simplest approach:

with counts as (
  select 
    count(*) as total,
    count(nullif(inbound, 0)) as inbound,
    count(nullif(inbound, 1)) as outbound
  from posts
  where accountid = 333
) select 
    * ,
    round((outbound / total) * 100) outboundPercentage
from counts;

MySQL 8.0 fiddle

Advertisement

Answer