This is an UNPIVOT problem.To get the sum of credit items and the sum of debit items you can use a SUM
of a CASE
statement. To show that, we need a table first:
SQL Fiddle
MySQL 5.5.32 Schema Setup:
CREATE TABLE invoice_items
(`invoice_item_id` int, `invoice_set_id` varchar(3), `credit_debit` varchar(6), `amount` int)
;
INSERT INTO invoice_items
(`invoice_item_id`, `invoice_set_id`, `credit_debit`, `amount`)
VALUES
(62, 'a22', 'debit', 15.00),
(63, 'a22', 'debit', 8.00),
(64, 'a22', 'credit', 23.00),
(65, 'b23', 'debit', 44.00),
(66, 'c55', 'debit', 15.00),
(67, 'c55', 'debit', 2.00),
(67, 'c55', 'credit', 8.00)
;
Now you can combine SUM
and CASE
like this:
Query 1:
SELECT invoice_set_id,
SUM(CASE WHEN credit_debit = 'debit' THEN amount END) AS debit_amount,
SUM(CASE WHEN credit_debit = 'credit' THEN amount END) AS credit_amount
FROM invoice_items
GROUP BY invoice_set_id;
Results:
| INVOICE_SET_ID | DEBIT_AMOUNT | CREDIT_AMOUNT |
|----------------|--------------|---------------|
| a22 | 23 | 23 |
| b23 | 44 | (null) |
| c55 | 17 | 8 |
From here it is fairly simple to filter out the unwanted rows. We just need to wrap the two sums in COALESCE
statements to turn NULL
into 0:
Query 2:
SELECT invoice_set_id
FROM(
SELECT invoice_set_id,
SUM(CASE WHEN credit_debit = 'debit' THEN amount END) AS debit_amount,
SUM(CASE WHEN credit_debit = 'credit' THEN amount END) AS credit_amount
FROM invoice_items
GROUP BY invoice_set_id
)X
WHERE COALESCE(debit_amount,0) > COALESCE(credit_amount,0);
Results:
| INVOICE_SET_ID |
|----------------|
| b23 |
| c55 |
If your actual problem is as simple as your example and you do not need the intermediate values for anything else, you can get the result also by just changing the sign of the amount based on the credit_debit
value before calculating the sum. That would look like this:
Query 3:
SELECT invoice_set_id
FROM invoice_items
GROUP BY invoice_set_id
HAVING SUM(CASE WHEN credit_debit = 'debit' THEN amount ELSE -amount END)>0;
Results:
| INVOICE_SET_ID |
|----------------|
| b23 |
| c55 |
Indeed, the single row accounting schema proposed allows to do proper double entry accounting (to always specify the account debited and credited) without introducing the redundancy of the "amount" data.
The one row schema gives you an implementation of a double entry that balances by construction, so that it is impossible to ever "loose balance". The machine may recompute the ledgers on the fly.
You have to do 2 select instead of one to retrieve a ledger.
Note, besides transaction split, there are other transactions such as foreign exchange might end up with 2 records instead of 4. It all depends if you would denormalize a bit just enter 4 transactions with similar description.
You may prevent the entry or modification of any transaction to maintain an audit trail, this is required if you want to be able to audit the transaction log.
It appears that in the thread above, for the CPA, "fully normalized" appears to mean rules recognized by all accountants, whereas for the programmer it has a diferent meaning, that there is no derived or redundant data stored.
All there is to accounting data is a set of transactions which give amount and the accounts from which and to which they flow, along with their date, some description (and other attachements). Ledgers and balances are simple views derived from this transactional data by doing sums.
Best Answer
Your Method 1 is correct. Here is how I implemented in an accounting system. This model is from "The Data Model Resource Book". How you store the data is very different from how accounting terminology models the double entry method. Credit can mean positive or negative depending on whether you're working on an asset or liability account. This schema will allow you to persist the data no matter what type of account you're posting to.