Query to compute unspecified values in “Other” category

The problem that we are trying to solve is explained herein using a simple (and deliberately sub-normalized) table.
The table is defined as follows:

create table expense_profile
(
  id  INT UNSIGNED AUTO_INCREMENT,
  name  VARCHAR(10),
  category  VARCHAR(16),
  percentage DECIMAL(15,2),
  
  PRIMARY KEY(id)
);  

The table is populated as follows:

INSERT INTO expense_profile(name, category, percentage)
VALUES('Alice', 'Housing', 15), ('Alice', 'Transportation', 5),
('Alice', 'Food', 25), ('Alice', 'Utilities', 5),
('Alice', 'Healthcare', 5), ('Alice', 'Personal', 3),
('Bob', 'Transportation', 7), ('Bob', 'Food', 18),
('Bob', 'Healthcare', 8), ('Bob', 'Personal', 20);

In this table we have accounted for 58% of Alice’s expenses and 53% of Bob’s expenses. Implied therein is that 42% of Alice’s expenses and 47% of Bob’s expenses are in the “Other” category.

Q) Is there a query that would allow us to list, for the specified user, all the categories, including the “Other” category?

For example, we would like to have the following output for Bob:

+------+----------------+------------+
| name | category       | percentage |
+------+----------------+------------+
| Bob  | Transportation |       7.00 |
| Bob  | Food           |      18.00 |
| Bob  | Healthcare     |       8.00 |
| Bob  | Personal       |      20.00 |
| Bob  | Other          |      47.00 |
+------+----------------+------------+

The following query would accomplish what you’re looking for:

SELECT name, category, percentage
FROM expense_profile
WHERE name = 'Bob'

UNION ALL

SELECT name, 'Other' AS category, 100.0 - SUM(percentage) AS percentage
FROM expense_profile
WHERE name = 'Bob'
GROUP BY name