Skip to content
Advertisement

how to calculate the previous total with the current total with group by

I need to calculate every month with the previous months like:

Jan: 5000

Feb: 2000 +(5000 from Jan) = 7000

for each account.

so how can I do it, I tried to extract the totals by each month but I didn’t get any result.

DDLs:

CREATE TABLE `tblregs1` (
`RegID` bigint(20) NOT NULL,
`RegRef` bigint(20) NOT NULL,
`RegCode` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`RegDate` date NOT NULL,
`RegCurr` int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tblregs1` (`RegID`, `RegRef`, `RegCode`, `RegDate`, `RegCurr`,         
`RegDetails`, `RegType`, `RegStatus`) VALUES
(1, 202010011140192, '', '2020-10-01', 1),
(2, 202010011141252, '', '2020-10-01', 1),
(3, 202010011141562, '', '2020-10-01', 1),
(4, 202010011143242, '', '2020-10-02', 0),
(5, 202010010103422, '', '2020-10-01', 1),
(6, 202010020836472, '', '2020-10-01', 0),
(7, 202010030101142, '', '2020-10-02', 0),
(8, 202010030101272, '', '2020-10-02', 0),
(9, 202010030101392, '', '2020-10-02', 0),
(10, 202010030101522, '', '2020-10-02', 0),
(11, 202010030102112, '', '2020-10-02', 0),
(12, 202010030153562, '', '2020-10-03', 1),
(14, 202010030158322, '', '2020-10-03', 1),
(15, 202010030431432, '', '2020-10-02', 0),
(16, 202010030439122, '', '2020-10-01', 1),
(17, 202010030512263, '', '2020-10-01', 1),
(18, 202010030515133, '', '2020-10-03', 2),
(19, 202010041255541, '', '2020-10-02', 1);

CREATE TABLE `tblregs2` (
`RegdID` bigint(20) NOT NULL,
`RegRef` bigint(20) NOT NULL,
`RegdM1` double NOT NULL DEFAULT '0',
`RegdD1` double NOT NULL DEFAULT '0',
`RegdAccID` int(11) NOT NULL,
`RegdDetails` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`RegdCurrPrice` double NOT NULL DEFAULT '0',
`RegdM2` double NOT NULL DEFAULT '0',
`RegdD2` double NOT NULL DEFAULT '0',
`RecStatus` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Normal',
`RegdNo` tinyint(4) NOT NULL,
`RegdMtbkRaseed` double NOT NULL DEFAULT '0',
`RegdMtbkFark` double NOT NULL DEFAULT '0',
`RegdMtbkStatus` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`RegdMtbkNotes` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tblregs2` (`RegdID`, `RegRef`, `RegdM1`, `RegdD1`,      
`RegdAccID`, `RegdDetails`, `RegdCurrPrice`, `RegdM2`, `RegdD2`,     
`RecStatus`, `RegdNo`, `RegdMtbkRaseed`, `RegdMtbkFark`, `RegdMtbkStatus`, 
`RegdMtbkNotes`) VALUES
(1, 202010011140192, 1000, 0, 916, 'Des1', 1, 1000, 0, 'Normal', 0, 0, 0, 
NULL, NULL),
(4, 202010011141252, 0, 10000, 32, 'Des1', 1, 0, 10000, 'Normal', 2, 0, 0, 
NULL, NULL),
(13, 202010011143242, 1680, 0, 32, 'Des1', 1, 1680, 0, 'Normal', 3, -8320, 
0, 'no', 'no'),
(14, 202010011143242, 0, 1680, 15, 'Des1', 1, 0, 1680, 'Normal', 4, 0, 0, 
NULL, NULL),
(16, 202010011143242, 0, 10, 32, 'Des1', 1, 0, 10, 'Normal', 6, 0, 0, 
NULL, NULL),
(17, 202010011143242, 15, 0, 32, 'Des1', 1, 15, 0, 'Normal', 7, -8315, 0, 
'', '-8315'),
(19, 202010011143242, 450000, 0, 32, 'Des1', 1235, 364.37, 0, 'Normal', 9, 
0, 0, '', ''),
(20, 202010011143242, 0, 450000, 306, 'Des1', 1, 0, 450000, 'Normal', 10, 
0, 0, NULL, NULL),
(33, 202010020836472, 0, 0, 916, '', 0, 0, 0, 'Normal', 9, 0, 0, NULL, 
NULL),
(34, 202010020836472, 0, 0, 86, '', 0, 0, 0, 'Normal', 10, 0, 0, NULL, 
NULL),
(35, 202010030153562, 1400, 0, 32, 'Des1', 32, 1400, 0, 'Normal', 0, 
-6550.63, 0, '', '12'),
(36, 202010030153562, 0, 1400, 55, 'Des1', 55, 0, 1400, 'Normal', 0, 0, 0, 
NULL, NULL),
(38, 202010030158322, 0, 7000, 32, 'Des1', 1, 0, 7000, 'Normal', 2, 0, 0, 
NULL, NULL),
(64, 202010041255541, 0, 4500, 32, 'Des1', 1, 0, 4500, 'Normal', 2, 0, 0, 
NULL, NULL),
(65, 202010041255541, 10, 0, 33, 'Des1', 1, 10, 0, 'Normal', 3, 0, 0, 
NULL, NULL),
(66, 202010041255541, 0, 10, 45, 'Des1', 1, 0, 10, 'Normal', 4, 0, 0, 
NULL, NULL);

example of my data for the specified user is:

+-------+-----------+--------+-----------+--------+--------+
| RegID |  RegDate  | RegdID | RegdAccID | RegdM2 | RegdD2 |
+-------+-----------+--------+-----------+--------+--------+
|   2   | 10/1/2020 |    4   |     32    |    0   |  10000 |
|   4   | 10/2/2020 |   13   |     32    |  1680  |    0   |
|   4   | 10/2/2020 |   16   |     32    |    0   |   10   |
|   4   | 10/2/2020 |   17   |     32    |   15   |    0   |
|   4   | 10/2/2020 |   19   |     32    | 364.37 |    0   |
|   12  | 10/3/2020 |   35   |     32    |  1400  |    0   |
|   14  | 10/3/2020 |   38   |     32    |    0   |  7000  |
|   19  | 10/2/2020 |   64   |     32    |    0   |  4500  |
+-------+-----------+--------+-----------+--------+--------+

this is the result that gives me:

+-----------+--------+--------+--------+-----------+
|  RegDate  |RegdAccID| RegdM2 | RegdD2 |   Result  |
+-----------+--------+--------+--------+-----------+
| 10/1/2020 |    4   |    0   |  10000 |   -10000  |
| 10/2/2020 |   13   |  1680  |    0   |   -8320   |
| 11/2/2020 |   13   |    0   |   10   |   -8330   |
| 10/2/2020 |   17   |   15   |    0   |   -8315   |
| 11/2/2020 |   17   | 364.37 |    0   |  -7950.63 |
| 11/2/2020 |   17   |    0   |  4500  | -18050.63 |
| 10/3/2020 |   35   |  1400  |    0   |  -6550.63 |
| 11/3/2020 |   35   |    0   |  7000  | -13550.63 |
+-----------+--------+--------+--------+-----------+

what I need now is: how to calculate the previous total with the current total for each account and month. I tried the next code but it didn’t work, it gives me the wrong numbers:

SELECT 
    d.RegdAccID,
    MONTH(d.RegDate) AS xMonth, 
    d.AccName, 
    IF(@w = d.RegdAccID,
    CAST(@s := @s + d.RegdM2-d.RegdD2 AS DECIMAL(15,2)),
    CAST(@s := 0 + d.RegdM2-d.RegdD2 AS DECIMAL(15,2))) AS Raseed,
    (@w := d.RegdAccID) as x
FROM 
    (SELECT 
        a.RegDate,  
        a.RegRef,  
        b.RegdAccID, 
        b.RegdM2, 
        b.RegdD2, 
        n.AccName
    FROM tblregs2 b
    INNER JOIN tblregs1 a ON b.RegRef = a.RegRef
    INNER JOIN tblaccounts n ON n.AccID = b.RegdAccID
    WHERE 
    (b.RegdM2>0 or b.RegdD2>0)) d
CROSS JOIN (SELECT @s := 0) c
CROSS JOIN (SELECT @w := 0) i
GROUP BY d.RegdAccID,xMonth
ORDER BY d.RegdAccID,xMonth

Advertisement

Answer

I solved part of the issue:

SELECT 
                                    month(d.RegDate) AS xMonth,  
                                    d.RegdAccID,                                  
                                    d.AccName,(@ss + SUM(d.Raseed)) xras
FROM 
                                    (SELECT 
                                    a.RegDate, 
   

                             b.RegdAccID, 
                                b.RegdM2, 
                                b.RegdD2, 
                                n.AccName,
                                IF(@w = b.RegdAccID,
                                CAST(@s := @s + b.RegdM2-b.RegdD2 AS DECIMAL(15,2)),
                                CAST(@s := 0 + b.RegdM2-b.RegdD2 AS DECIMAL(15,2))) AS Raseed,
                                (@w := b.RegdAccID) as x 
                                 
                                FROM tblregs2 b
                                INNER JOIN tblregs1 a ON b.RegRef = a.RegRef
                                INNER JOIN tblaccounts n ON n.AccID = b.RegdAccID  
                                WHERE 
                                (b.RegdM2>0 or b.RegdD2>0) AND b.RegdAccID=112) d
                                CROSS JOIN (SELECT @s := 0) c
                                CROSS JOIN (SELECT @ss := 0) cc
                                CROSS JOIN (SELECT @w := 0) i
                                GROUP BY xMonth
                                ORDER BY 
                                d.RegdAccID,xMonth

but I want the CurrentTotal to be (PreviousTotal+Current)

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement