PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Friday, January 14, 2022

[FIXED] Is it posible to create some field with math function in SQL? if yes, how should i do it in this problem?

 January 14, 2022     mariadb, mariadb-10.4, phpmyadmin, sql     No comments   

Issue

I'm working on a SQL table that save money transaction for every day. this is my table design:

    CREATE TABLE `transaction` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `date` datetime NOT NULL,
 `member_id` int(11) NOT NULL,
 `name` varchar(60) CHARACTER SET utf8 DEFAULT NULL,
 `balance_lastMonth` int(11) NOT NULL,
 `income` int(11) NOT NULL,
 `outcome` int(11) NOT NULL,
 `balance` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `member_id` (`member_id`),
 CONSTRAINT `transaction_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`)
) ENGINE=InnoDB CHARSET=latin1

the balance field formula : balance_lastMonth + income - outcome and balance_lastMonth is balance in the last month

is it possible to achieve it in one table? if yes how to do it? or may be there is better way to do it. i'm using 10.4.6-MariaDB.


Solution

You can calculate the balance using a query:

select t.*,
       sum(income - outcome) over (partition by member_id order by date) as balance,
       sum(income - outcome) over (partition by member_id order by date) - (income - outcome) as balance_lastmonth
from transaction t;

The simplest thing to do is to encapsulate this in a view and just use that.

If you actually want to store the results in the table, you'll need to use a trigger. I don't recommend that approach unless you have some sort of requirement that the balances be stored.



Answered By - Gordon Linoff
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

1,254,466

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © 2025 PHPFixing