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');

CodePudding user response：

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;

Another approach is to left join in one direction and then union with the result of the left join in the other direction -

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

UNION ALL

SELECT
    e.branch,
    0,
    e.amount,
    0 - e.amount
FROM (
    SELECT branch, SUM(amount) amount FROM expenses GROUP BY branch
) e
LEFT JOIN payments p ON p.branch = e.branch
WHERE p.branch IS NULL

The first part of the query gets the aggregates for branches which exist in both payments and expenses, or just payments. The second part of the query gets the aggregates for branches which only exists in expenses.