Is there possible query to compute for the total profits per category in 2 table?

How do I do.

Here is the table 1 for payments Payments

Here is the table 2 for expenses Expenses

and I want to get the total profit of this table per branch here is the query I use:

SELECT payments.branch , SUM(payments.amount) AS Profits, SUM(expenses.amount) AS Expenses, SUM(payments.amount)  - SUM(expenses.amount) AS Total
FROM payments
RIGHT OUTER JOIN expenses
on payments.branch = expenses.branch
GROUP BY payments.branch

I tried the above query but there result is like this:

Expected result

IF YOU WANT TO TRY IT ON YOUR MACHINE HERE IS THE QUERY

CREATE TABLE `expenses` (
  `expensesid` int(255) NOT NULL,
  `branch` varchar(255) NOT NULL,
  `typeofexpenses` varchar(255) NOT NULL,
  `amount` int(255) NOT NULL,
  `note` varchar(255) NOT NULL,
  `dateofexpenses` datetime NOT NULL
);


INSERT INTO `expenses` (`expensesid`, `branch`, `typeofexpenses`, `amount`, `note`, `dateofexpenses`) VALUES
(2, 'Santo', 'Electricity', 299, 'aadadad', '2022-12-27 00:00:00'),
(3, 'Maligno', 'Electricity', 20, 'daawd', '2022-12-27 00:00:00'),
(4, 'Santo', 'Electricity', 11111, 'adawd', '2022-12-27 00:00:00'),
(5, 'Santo', 'Electricity', 30, 'ef', '2022-12-27 00:00:00'),
(7, 'Santo', 'Electricity', 100, 'we', '2023-01-17 19:56:26'),
(8, 'Santo', 'Electricity', 200, 'dw', '2022-12-25 15:45:49');

CREATE TABLE `payments` (
  `paymentid` int(11) NOT NULL,
  `Unit` varchar(255) NOT NULL,
  `branch` varchar(255) NOT NULL,
  `tenantid` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `amount` int(11) NOT NULL,
  `note` varchar(255) NOT NULL,
  `dateofpayment` datetime NOT NULL
);

INSERT INTO `payments` (`paymentid`, `Unit`, `branch`, `tenantid`, `name`, `amount`, `note`, `dateofpayment`) VALUES
(1, 'Bodega', 'Santo', 1, 'Alsace Alsace', 10000, 'REFRESHED DATA', '2022-12-27 16:22:53'),
(2, 'Bodega', 'Santo', 1, 'Alsace Alsace', 1333, 'wawdad', '2022-11-22 19:17:45'),
(3, 'Bodega', 'Jacinto MRT', 1, 'Alsace Alsace', 1000, 'dwadawdaw', '2023-01-01 19:36:13'),
(4, 'Bodega', 'Jacinto MRT', 4, 'awd awdawd', 2000, 'awd', '2022-12-25 15:45:49');

One way of doing this is to start with the full list of branches (subquery b) and then left join to the aggregated data of the other two tables (sub queries p & e) -

SELECT
    b.branch AS Branch,
    IFNULL(p.amount, 0) AS Payments,
    IFNULL(e.amount, 0) AS Expenses,
    IFNULL(p.amount, 0) - IFNULL(e.amount, 0) AS Profit
FROM (SELECT DISTINCT branch FROM expenses UNION SELECT DISTINCT branch FROM payments) AS b
LEFT JOIN (SELECT branch, SUM(amount) amount FROM payments GROUP BY branch) AS p ON b.branch = p.branch
LEFT JOIN (SELECT branch, SUM(amount) amount FROM expenses GROUP BY branch) AS e ON b.branch = e.branch;

As you are using MariaDB >= 10.2.1 we can use Common Table Expressions to improve readability. And as you have stated that you have a separate table for branches, I will assume branches (branchid, name) and branchid instead of branch in expenses and payments tables.

WITH payments AS (
    SELECT branchid, SUM(amount) total FROM payments GROUP BY branchid
), expenses AS (
    SELECT branchid, SUM(amount) total FROM expenses GROUP BY branchid
)
SELECT
    b.name AS Branch,
    IFNULL(p.total, 0) AS Payments,
    IFNULL(e.total, 0) AS Expenses,
    IFNULL(p.total, 0) - IFNULL(e.total, 0) AS Profit
FROM branches AS b
LEFT JOIN payments AS p ON b.branchid = p.branchid
LEFT JOIN expenses AS e ON b.branchid = e.branchid;