Calculate percentage on an MySQL query-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You can simply calculate total number of posts, total in-bounds, out-bounds and percentage within a sub query and then join the sub query with account.

SELECT p.total,
       p.outbound,
       p.inbound,
       p.outboundPercentage
FROM `account` AS a
JOIN
  (SELECT accountid,
          COUNT(`id`) AS total,
          SUM(IF(inbound=1
                 AND accountid=333, 1, 0)) AS outbound,
          SUM(IF(inbound=0
                 AND accountid=333, 1, 0)) AS inbound,
          ROUND(((SUM(IF(inbound=1
                         AND accountid=333, 1, 0)) / COUNT(`id`)) * 100), 2) AS outboundPercentage
   FROM posts
   GROUP BY accountid) AS p ON p.accountid = a.id
WHERE a.id = 333
GROUP BY a.id