mySQL debit credit balances

Question

Is it possible to have a mySQL query to get these fields old balance (sum of my expenses), debit (here should I put total my expenses with same id in the current date), credit (not much in these query), new balance (same as debit).

Now when the day was ended, the new balance will be added to the old balance, and a new balance will be form for the current date. I have my code but it does not calculate my previous balances.

Table: rf_expenses

--------------------------------------
id_rf_expenses   rf_expense_desc
--------------------------------------
1                    salary
2                    bonus
3                    transportation

Table: expenses

-----------------------------------------------------------
id_expnses    id_rf_expenses     expense_amt   expense_date
------------------------------------------------------------
1                1                  100           current
2                1                  100           yesterday
3                2                   50           current
4                2                   50           current
5                3                  200           yesterday

OUTPUT:

-----------------------------------------------------------
EXPENSE      OLD BALANCE    DEBIT     CREDIT   NEW BALANCE
-----------------------------------------------------------
SALARY           100         100        0          100          
BONUS             0          100        0          100
TRANSPO          200           0        0            0

This will happen every query of the current date..any dates previous the the current will be sum up in the old balance

SELECT 
    r.rf_expense_desc, 
    COALESCE(SUM(expense_amt), 0) - COALESCE(q1.amt2, 0) OLD, 
    COALESCE(q1.amt2, 0) AS NEW
FROM 
    rf_expenses r
LEFT JOIN 
    expenses e ON r.id_RF_expense = e.id_rf_expense
LEFT JOIN 
    (SELECT 
         SUM(expense_amt) amt2, id_expense, 
         rf_expenses.id_rf_expense
     FROM 
         rf_expenses
     LEFT JOIN 
         expenses ON rf_expenses.id_RF_expense = expenses.id_rf_expense
     WHERE 
         expense_date = CURDATE( ) 
     GROUP BY 
         rf_expense_desc, expense_date) q1 ON r.id_rf_expense = q1.id_rf_expense
GROUP BY 
    rf_expense_desc 

Answer 1

Hi I give two queries: one with date type column and another with string.

1 Query (with your sample data) SQLFIDDLE example:

    SELECT 
rf.rf_expense_desc as EXPENSE      
,(SELECT 
  COALESCE(SUM(e.expense_amt),0)
  FROM expenses e
  WHERE e.id_rf_expenses= rf.id_rf_expenses
  AND expense_date != 'current'
 ) AS 'OLD BALANCE'
,(SELECT 
  COALESCE(SUM(e.expense_amt),0)
  FROM expenses e
  WHERE e.id_rf_expenses= rf.id_rf_expenses
  AND expense_date = 'current'
 ) AS 'DEBIT'
,0 AS CREDIT
,(SELECT 
  COALESCE(SUM(e.expense_amt),0)
  FROM expenses e
  WHERE e.id_rf_expenses= rf.id_rf_expenses
  AND expense_date = 'current'
 ) AS 'NEW BALANCE'
FROM 
rf_expenses rf

2 Query (with data type date) SQLFIddle example2:

SELECT 
rf.rf_expense_desc as EXPENSE      
,(SELECT 
  COALESCE(SUM(e.expense_amt),0)
  FROM expenses e
  WHERE e.id_rf_expenses= rf.id_rf_expenses
  AND expense_date != CURDATE()
 ) AS 'OLD BALANCE'
,(SELECT 
  COALESCE(SUM(e.expense_amt),0)
  FROM expenses e
  WHERE e.id_rf_expenses= rf.id_rf_expenses
  AND expense_date = CURDATE()
 ) AS 'DEBIT'
,0 AS CREDIT
,(SELECT 
  COALESCE(SUM(e.expense_amt),0)
  FROM expenses e
  WHERE e.id_rf_expenses= rf.id_rf_expenses
  AND expense_date = CURDATE()
 ) AS 'NEW BALANCE'
FROM 
rf_expenses rf

Result:

|        EXPENSE | OLD BALANCE | DEBIT | CREDIT | NEW BALANCE |
---------------------------------------------------------------
|         salary |         100 |   100 |      0 |         100 |
|          bonus |           0 |   100 |      0 |         100 |
| transportation |         200 |     0 |      0 |           0 |

Answer 2

Yet another example of why mysql should enforce the SELECTs of all GROUP BY clauses to have only columns specified in the GROUP BY clause, or surrounded by an aggregate function. Mysql call's this bug a "feature".

What I mean is, take a look at the SELECT clauses for both of your GROUP BYs, both the inner query and the outer. You have columns that you are selecting that are not in the GROUP BY clause and aren't surrounded by a SUM, AVG, COUNT or other aggregate function. If you group rows, and you dont tell mysql how to "reduce" the rows in the group it will simply take any value at random.

For example, suppose we have this data in a table called "test":

PARENTID     ID     VALUE
-------------------------
1            1      2.05
1            2      3.50
1            3      1.58
2            1      4.65
2            2      0.65

The we query: SELECT parentid, value FROM test GROUP BY parentid, value

Well we havent said what to do with the VALUE column, for example to SUM, so mysql will simply take any value in the group, for PARENTID=1 we may get any of 2.05, 3.50, or 1.58...but not the combined lot of them.

So this is correct:

SELECT parentid, SUM(value) as total FROM test GROUP BY parentid, value;

Now when mysql reduces each group we get the correct total sum, our results are:

PARENTID          VALUE
-------------------------
1                 7.13
2                 5.30

So in your example, you probably want to query:

SELECT r.rf_expense_desc, expense_date,
    COALESCE( SUM( expense_amt ) , 0 ) - COALESCE( sum(q1.amt2), 0 ) OLD, COALESCE( sum(q1.amt2), 0 ) AS NEW
FROM rf_expenses r
LEFT JOIN expenses e ON r.id_RF_expense = e.id_rf_expense
LEFT JOIN (
    SELECT SUM( expense_amt ) amt2, count(id_expense) as counts
    FROM rf_expenses
    LEFT JOIN expenses ON rf_expenses.id_RF_expense = expenses.id_rf_expense
    WHERE expense_date = CURDATE( ) 
    GROUP BY rf_expense_desc, expense_date
) q1 ON r.id_rf_expense = q1.id_rf_expense
GROUP BY rf_expense_desc