From the sample database,
0037FirebirdSQLGroup.odb (4.9 KB)
0037FirebirdSQLGroup.ods (17.7 KB)
Query: TrialBalanceWithDate
SELECT
"JND"."AccountCodeLevel1" "AC1",
"JND"."AccountCodeLevel2" "AC2",
"JND"."AccountCodeLevel3" "AC3",
( COALESCE ( "JND"."DebitAmount", 0 ) ) - ( COALESCE ( "JND"."CreditAmount", 0 ) ) "DRCR"
FROM
"JournalDetail" "JND",
"JournalNumber" "JNN"
WHERE
"JND"."JournalNumber" = "JNN"."Batch"
AND
"JNN"."TransactionDate" >= '2022-07-01'
AND
"JNN"."TransactionDate" <= '2022-07-31'
ORDER BY
"AC1" ASC,
"AC2" ASC,
"AC3" ASC,
"JNN"."TransactionDate" ASC
The result:
| AC1 | AC2 | AC3 | DRCR |
|---|---|---|---|
| 1 | 1 | 1 | -1200 |
| 1 | 1 | 1 | 5000 |
| 1 | 1 | 1 | -13700 |
| 1 | 2 | 1 | 1200 |
| 1 | 5 | 1 | 1500 |
| 2 | 2 | 3 | -1500 |
| 4 | 1 | 1 | -5000 |
| 5 | 1 | 5 | 4200 |
| 5 | 1 | 5 | 4500 |
| 5 | 7 | 1 | 5000 |
How can I group AC1, AC2 and AC3 and get the result like this ?
| AC1 | AC2 | AC3 | DRCR |
|---|---|---|---|
| 1 | 1 | 1 | -9900 |
| 1 | 2 | 1 | 1200 |
| 1 | 5 | 1 | 1500 |
| 2 | 2 | 3 | -1500 |
| 4 | 1 | 1 | -5000 |
| 5 | 1 | 5 | 8700 |
| 5 | 7 | 1 | 5000 |
Query: TrialBalanceWithPeriod
SELECT
CAST( EXTRACT( YEAR FROM CAST( "JNN"."TransactionDate" AS DATE ) ) * 100 + EXTRACT( MONTH FROM CAST( "JNN"."TransactionDate" AS DATE ) ) * 1 AS VARCHAR ( 6 ) ) "PRD",
"JND"."AccountCodeLevel1" "AC1",
"JND"."AccountCodeLevel2" "AC2",
"JND"."AccountCodeLevel3" "AC3",
( COALESCE ( "JND"."DebitAmount", 0 ) ) - ( COALESCE ( "JND"."CreditAmount", 0 ) ) "DRCR" FROM
"JournalDetail" "JND",
"JournalNumber" "JNN"
WHERE
"JND"."JournalNumber" = "JNN"."Batch"
AND
"JNN"."TransactionDate" >= '2022-07-01'
AND
"JNN"."TransactionDate" <= '2022-08-31'
ORDER BY
"PRD" ASC,
"AC1" ASC,
"AC2" ASC,
"AC3" ASC
The result:
| PRD | AC1 | AC2 | AC3 | DRCR |
|---|---|---|---|---|
| 202207 | 1 | 1 | 1 | -1200 |
| 202207 | 1 | 1 | 1 | -13700 |
| 202207 | 1 | 1 | 1 | 5000 |
| 202207 | 1 | 2 | 1 | 1200 |
| 202207 | 1 | 5 | 1 | 1500 |
| 202207 | 2 | 2 | 3 | -1500 |
| 202207 | 4 | 1 | 1 | -5000 |
| 202207 | 5 | 1 | 5 | 4200 |
| 202207 | 5 | 1 | 5 | 4500 |
| 202207 | 5 | 7 | 1 | 5000 |
| 202208 | 1 | 1 | 1 | -3000 |
| 202208 | 1 | 1 | 1 | 6000 |
| 202208 | 1 | 1 | 1 | 9600 |
| 202208 | 2 | 2 | 3 | 3000 |
| 202208 | 4 | 1 | 1 | -6000 |
| 202208 | 4 | 1 | 1 | -9600 |
How can I group AC1, AC2 and AC3 and get the result like this ?
| PRD | AC1 | AC2 | AC3 | DRCR |
|---|---|---|---|---|
| 202207 | 1 | 1 | 1 | -9900 |
| 202207 | 1 | 2 | 1 | 1200 |
| 202207 | 1 | 5 | 1 | 1500 |
| 202207 | 2 | 2 | 3 | -1500 |
| 202207 | 4 | 1 | 1 | -5000 |
| 202207 | 5 | 1 | 5 | 8700 |
| 202207 | 5 | 7 | 1 | 5000 |
| 202208 | 1 | 1 | 1 | 12600 |
| 202208 | 2 | 2 | 3 | 3000 |
| 202208 | 4 | 1 | 1 | -15600 |
LibreOffice:
Version: 7.3.7.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.1
Calc: threaded
Base: Firebird
OS:
Ubuntu 22.04
