I am trying to populate data table using SQL query from MySQL/MariaDB, to display it as Trial Balance for a report.
My DB engine is MariaDB 10.4.19
For this instance.. I have 2 tables.
- Journal (Contains the transactions) and
| id | date | debit | credit| amount |
|----|------------|-------|-------|-----------|
| 1 | 2021-09-01 | 8 | 2 | 5000.000 |
| 6 | 2021-09-22 | 22 | 17 | 4750.000 |
| 8 | 2021-09-05 | 8 | 3 | 1485.000 |
| 9 | 2021-08-10 | 8 | 6 | 108.000 |
| 10 | 2021-07-07 | 8 | 23 | 98756.000 |
- Ledgers Account Names
| id | name | desc | status |
|----|-----------------------------------|-----------------------------------|--------|
| 1 | Assets | Current Assets | 1 |
| 2 | Stockholders equity | Stockholders or Owners equity | 1 |
| 3 | Liability | Liabilities related accounts | 1 |
| 4 | Operating Revenues | Operating Revenues | 1 |
| 5 | Operating Expenses | Operating Expenses | 1 |
| 6 | Non-operating revenues and gains | Non-operating revenues and gains | 1 |
| 7 | Non-operating expenses and losses | Non-operating expenses and losses | 1 |
| 8 | Cash | For cash transaction | 1 |
Now from my program, if I select an account (populated from ledgers) and submit it will send its id. With that account id I have populate all the transactions against that account id.
For Example: if i chose cash account it will send lets say it will send 8 as id.
now
- the SQL query has to populate date and amount from journal, and account name from ledger - name of account that is against the id 8 in a transaction. for example if it is 2 then it shall return "Stockholders equity" as name.
- The amount should be in credit column if the given id 8 in debit or the amount should be in debit column if the given id 8 is in credit
and as in above points, from program, if account id is passed as 8 (i.e. "cash") then the DESIRED OUPUT of a SQL SELECT Query result should look like this...
| Date | Account | Debit | Credit |
|------------|----------------------------------|---------:|----------:|
| 2021-09-01 | Stockholders equity | 0.00 | 5000.00 |
| 2021-09-05 | Liability | 0.00 | 1485.00 |
| 2021-08-10 | Non-operating revenues and gains | 0.00 | 108.00 |
| 2021-07-07 | Land | 0.00 | 98756.00 |
| 2021-02-25 | Land | 21564.00 | 0.00 |
| 2021-01-19 | Vehicles | 0.00 | 23132.00 |
| 2020-05-19 | Buildings Asset | 465.00 | 0.00 |
| 2019-09-01 | Non-operating revenues and gains | 315.00 | 0.00 |
| 2019-05-23 | Land | 346.00 | 0.00 |
| 2018-04-12 | Cash | 0.00 | 697.00 |
| 2017-05-15 | Non-operating revenues and gains | 0.00 | 999.00 |
| 2018-06-18 | Operating Revenues | 0.00 | 496.00 |
| 2018-06-23 | Liability | 0.00 | 426.00 |
| 2019-12-12 | Assets | 0.00 | 1684.00 |
| 2018-07-15 | Land | 0.00 | 1649.00 |
| 2018-07-22 | Land | 3666.00 | 0.00 |
| 2018-05-14 | Non-operating revenues and gains | 0.00 | 489.00 |
| 2018-09-16 | Equipment | 692.00 | 0.00 |
| 2021-04-18 | Non-operating revenues and gains | 4986.00 | 0.00 |
| 2020-04-19 | Land | 4956.00 | 0.00 |
| 2019-03-15 | Buildings Asset | 0.00 | 4988.00 |
| 2019-12-04 | Inventory | 0.00 | 7946.00 |
| 2019-08-25 | Stockholders equity | 0.00 | 19449.00 |
| | | | |
| | Total | 36990.00 | 167304.00 |
| | Balance | | 130314.00 |
here is the sample data in SQLFiddle http://sqlfiddle.com/#!9/fe2a00/2
Can you please give me an idea on how to populate this result table with SQL query using joins and/or unions or should I use some other way...
Please help with answer!
CodePudding user response：
Without Total:
SELECT journal.date `Date`,
ledger.name Account,
CASE WHEN journal.dracc = 8
THEN 0.000
ELSE amount
END Debit,
CASE WHEN journal.dracc = 8
THEN amount
ELSE 0.000
END Credit
FROM journal
JOIN ledger ON (ledger.id, 8) IN ((dracc, cracc), (cracc, dracc));
With Total:
SELECT CASE WHEN NOT GROUPING(journal.id)
THEN MAX(journal.date)
END `Date`,
CASE WHEN NOT GROUPING(journal.id)
THEN MAX(ledger.name)
ELSE 'Total'
END Account,
SUM(CASE WHEN journal.dracc = 8
THEN 0
ELSE amount
END) Debit,
SUM(CASE WHEN journal.dracc = 8
THEN amount
ELSE 0
END) Credit
FROM journal
JOIN ledger ON (ledger.id, 8) IN ((dracc, cracc), (cracc, dracc))
GROUP BY journal.id WITH ROLLUP;
With Total and Balance:
WITH
cte AS (
SELECT CASE WHEN NOT GROUPING(journal.id)
THEN MAX(journal.date)
END `Date`,
CASE WHEN NOT GROUPING(journal.id)
THEN MAX(ledger.name)
ELSE 'Total'
END Account,
SUM(CASE WHEN journal.dracc = 8
THEN 0
ELSE amount
END) Debit,
SUM(CASE WHEN journal.dracc = 8
THEN amount
ELSE 0
END) Credit
FROM journal
JOIN ledger ON (ledger.id, 8) IN ((dracc, cracc), (cracc, dracc))
GROUP BY journal.id WITH ROLLUP
)
SELECT *
FROM cte
UNION ALL
SELECT NULL, 'Balance', NULL, Credit - Debit
FROM cte
WHERE `Date` IS NULL;
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=794f87b054848b1584a6c8dd2dd5d47c
If your MySQL version is 5.x then:
- replace
WHEN NOT GROUPING(journal.id)withWHEN journal.id IS NOT NULL - convert CTE to subquery