How to create a calculated row in sql or power bi?

I am trying to do a calculation on 2 rows on a SQL I wrote so I can have a 3 row that will be Profit and show the amount is it possible?

This dummy data not true to any company!

see below :

SELECT a.pcg_type GL_Acoount_Group,
     Abs(sum(b.debit-b.credit)) GL_Amount
FROM dolibarr.llx_accounting_account a
JOIN dolibarr.llx_accounting_bookkeeping b
  ON a.account_number = b.numero_compte
WHERE a.pcg_type IN ('INCOME', 'EXPENSE')
    ANDa.fk_pcg_version = 'PCG99-BASE'
GROUP BY a.pcg_type

Results:

Group. Amt

INCOME 379200

EXPENSE 65700

Expected Results:

Group. Amt

INCOME 379200

EXPENSE 65700

PROFIT 313500

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Use ROLLUP for adding an extra row and use CASE statement inside SUM() function for treating expense value as negative for calculation

--MySQL
SELECT COALESCE(acc_type, 'Profit') "Group"
     , ABS(SUM(CASE WHEN acc_type = 'EXPENSE' THEN -amount ELSE amount END)) amt
FROM test
GROUP BY acc_type WITH ROLLUP

Another way by using UNION ALL

SELECT acc_type "Group"
     , SUM(amount) Amt
FROM test
GROUP BY acc_type

UNION ALL

SELECT 'Profit' AS "Group"
     , SUM(CASE WHEN acc_type = 'EXPENSE' THEN -amount ELSE amount END) Amt
FROM test

Please check this url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f859036ffcb3d808330bce5346daee1e

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0