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

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

CodePudding user response：

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