I have a table with my orders containg order no., total price, coupon code.
i am create a affiliate app in which the coupon code changes from one to one (user specific)
we need to show to app user with BIG Numbers.
total orders happened with your coupon code,
total amount happened with your code,
user share of 3% in total amount,
settled amount till date,
5.amount need to settle(have a new column as settlement, if we update as settle amount to update)
This might mean a relation from your users table (with the coupon code) to the orders table, then a rollup to count total orders, and another rollup to calculate the total amount.
From that, you can use a math column to calculate the user share, but that would mean an all-time value.
You can have a new table for settlements, record the user’s ID, and use a relation + rollup combo to get the total amount that has been settled.
Add a math column to subtract the settled amount from the total amount. That will be your “amount that needs to be settled” number.