-2

My query is I have Name column, Amount column, month column.

example if name is Sandeep he is having amount 90 in month Jan like below

Name    Amount   month new column
Sandeep  90      Jan    Jan(amount)=90
Sandeep  100     Feb.    (jan+feb)amount=190
Sandeep  120     March  (jan+feb+march)=310
Mandeep  70      April   (0+0+0+April)=70
Mandeep  20      May      (0+0+0+70+20)=90

I want the new column with the same format as above using MySQL query

Barmar
  • 741,623
  • 53
  • 500
  • 612
Dimple
  • 1
  • 3
  • 1
    Its not a good idea to store the month as a string. Even I don't know, what if the next year starts? will you erase all you old data because the amount of jan this year will otherwise be the amount of next year? – Thallius Nov 12 '21 at 16:12
  • So when there's a new month, it should add a new row for every name where the amount is the sum of all the previous amounts for that name? I'm a little unclear what the end result should be – Jonathan Clark Nov 12 '21 at 16:15
  • Sorry there is another column called year where it will be shown year – Dimple Nov 12 '21 at 16:15
  • Why does sandeep not appear in april and may? Also it's best to show sample data in tabular form. – P.Salmon Nov 12 '21 at 16:17
  • Hi Jonathan yes as you said it's right @JonathanClark – Dimple Nov 12 '21 at 16:17
  • @P.Salmon because it is null. – Dimple Nov 12 '21 at 16:18
  • What is null in this context? and what if sandeep missed in april and paid in may , or is that not possible? – P.Salmon Nov 12 '21 at 16:18
  • @JonathanClark each name has values based on month so I want a column like if sandeep has jan value then the column should show jan amount, – Dimple Nov 12 '21 at 16:19
  • @JonathanClark if he is having feb then the new column should have jan+feb amount – Dimple Nov 12 '21 at 16:20
  • If you change the `month` column to be numeric, you can use [this](https://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql) to calculate running totals. Doing it with names will be harder, but you can use the [`FIELD()`](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_field) function. – Barmar Nov 12 '21 at 16:20
  • then the may column should e like jan+feb+march+april+may as april has no amount it will be 90+100+120+0+something will be the column – Dimple Nov 12 '21 at 16:21

1 Answers1

0

I would suggest a complete other approach.

Store your monthly data in one row per name. Make your data an Integer containing Year and Month

CREATE TABLE t
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    yearmonth INT,
    amount INT
);

INSERT INTO t (name, yearmonth, amount) 
    VALUES ('Sandeep', 202101, 90),
           ('Sandeep', 202102, 100),    
           ('Sandeep', 202103, 120),    
           ('Mandeep', 202104, 70), 
           ('Mandeep', 202105, 20);

Then you can easily request all data you need For example:

SELECT name, SUM(amount)
FROM t
WHERE name = 'Sandeep' AND yearmonth BETWEEN 202101 AND 202103

I have created a little fiddle for you.

https://www.db-fiddle.com/f/gZ1u7yB65FddjfKLU92tHE/0

Thallius
  • 2,482
  • 2
  • 18
  • 36