How do I calculate the column in PHP union all function

Developer Account

I have an issue while calculating the column-like bank statement. actually, I am trying to calculate Opening Balance/today In /today Out / Closing Bal in MySQL PHP. I have two tables. material_infor `IN +` entry and material_approvefor `OUT -`. I am considering my opening balance was `123`.

I am new in PHP and I am here using Union All function. Please help me to create the function for getting Opening / In today / out today/closing calculation in my code.

`table- material_in`

id	components_key	insert_date	credit
1	12345	2021-04-16	100
2	12345	2021-04-16	50

`table- material_approve`

id	components_key	insert_date	debit
1	12345	2021-04-16	30
2	12345	2021-04-16	2

<pre>I am trying the code -

SELECT
    \`u\`.\`component\_key\`,
    \`u\`.\`insert\_date\`,
    \`u\`.\`transaction\_qty\`
FROM
    (
    SELECT
        \`components\_key\` AS \`component\_key\`,
        \`insert\_date\`,
        \`credit\` AS \`transaction\_qty\`
    FROM
        \`material\_in\`
    UNION ALL
    SELECT
        \`components\_key\`,
        \`insert\_date\`,
        \`debit\` AS \`transaction\_qty\`
    FROM
        \`material\_approve\`
    ) u
WHERE
    \`u\`.\`component\_key\` = '12345'
ORDER BY
    \`u\`.\`insert\_date\`
DESC

the output comes -

components_key	insert_date	transaction_qty
12345	2021-04-16	100
12345	2021-04-16	50
12345	2021-04-16	30
12345	2021-04-16	2

`output will be-`

components_key	insert_date	openbal	today_in	today_out	closebal
12345	2021-04-16	123	100	30	193
12345	2021-04-16	193	243	2	241

//openbal + today_in - today_out = closebal

SeanChupas · 2021-04-16

This is not a php issue. This is Mysql. Where are you stuck?