Question 20360 in Mysql Question posted in
The official MySQL documentation can be found here.

How to create a query that create sum of previous and current year total amount, base on multiple rows data? – Mysql

I wanted to create a query based on the date, I want to sum the credit and debit based on the year of the transaction_line and display it as total_previous (date within 2021) and total_current (date within 2022). Below is what I do and what is the expected result.

transaction_lines table:

account_id date debit credit
1021 2021-11-08 100 0
1021 2022-01-02 107 0
1021 2022-04-22 0 50
1021 2022-04-22 12 0

What I got currently:

account_id total_previous total_current
1021 0 -169

Expected result:

account_id total_previous total_current
1021 -100 -69

The query that I executed:

SELECT 
    account_id,
    date,
    debit,
    credit,
    IF(date >= '2021-01-01'
            AND date <= '2021-12-31',
        - COALESCE(SUM(debit), 0) + COALESCE(SUM(credit), 0),
        0) AS total_previous,
    IF(date >= '2022-01-01'
            AND date <= '2022-12-31',
        - COALESCE(SUM(debit), 0) + COALESCE(SUM(credit), 0),
        0) AS total_current
FROM
    transaction_lines
WHERE
    account_id = '1021'
        AND date >= '2021-01-01'
        AND date <= '2022-12-31';

I already work on this for a full day, thus I need help to justify if it is actually achievable. If it is possible, please advise me on what is wrong with my query.

Tags:

2

Answers


  1. 0

    Try this:

    CREATE TABLE transactions (
  account_id INT NOT NULL,
  date DATE NOT NULL,
  debit DECIMAL(10,2),
  credit DECIMAL(10,2)
);

INSERT INTO transactions (account_id, date, debit, credit) 
VALUES 
  (1021, '2021-11-08', 100.00, 0.00),
  (1021, '2022-01-02', 107.00, 0.00),
  (1021, '2022-04-22', 0.00, 50.00),
  (1021, '2022-04-22', 12.00, 0.00);

SELECT account_id
      ,SUM(CASE WHEN YEAR(date) = 2021 THEN -Debit+Credit END) AS total_previous
      ,SUM(CASE WHEN YEAR(date) = 2022 THEN -Debit+Credit END) AS total_current
FROM transactions
GROUP BY account_id

    enter image description here

    Login or Signup to reply.
  2. 0

    Instead of hard coding the year in the query, you can make the query dynamic so that it you can see the current_total and prev_total for multiple years.

    SELECT 
    current.account_id,
    current.current_year,
    current.current_total,
    prev.current_year as prev_year,
    prev.current_total AS prev_total
FROM
    (SELECT 
        t2.account_id,
            DATE_FORMAT(date, '%Y') AS current_year,
            SUM(t2.credit - t2.debit) AS current_total
    FROM
        transactions t2
    GROUP BY t2.account_id , DATE_FORMAT(date, '%Y')) `current`
        LEFT JOIN
    (SELECT 
        t2.account_id,
            DATE_FORMAT(date, '%Y') AS current_year,
            SUM(t2.credit - t2.debit) AS current_total
    FROM
        transactions t2
    GROUP BY t2.account_id , DATE_FORMAT(date, '%Y')) `prev` ON (current.account_id = prev.account_id
        AND (current.current_year - 1) = prev.current_year)

    Then when you want for a certain year you can just add a where clause
    e.g where current.current_year = 2021

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search