Search code examples
mariadbmariadb-10.5

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

paymentid Unit branch tenantid name amount note dateofpayment
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

Here is the table 2 for expenses Expenses

expensesid branch typeofexpenses amount note dateofexpenses
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

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:

REsult of Query

Expected result

Branch Payments Expenses Profit
Santo 11333 11740 -407
Jacinto MRT 3000 0 3000
Maligno 0 20 -20

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

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