SQL: Tax Calculation for Online Tax Application

In the world of finance, tax calculation is a fundamental operation. A development team is working on an online tax application designed to simplify tax calculations for users. One of the key features they aim to implement is a tax calculation mechanism. This mechanism will calculate a fixed 20% tax on the total income for each account in the system.

The results should have the following columns: iban | total_income | tax_rate calculated_tax.

iban-the IBAN of the account

total_income – the total income for the account, with two decimal places, including trailing zeros if necessary, e.g., 500.00

tax_rate- the tax rate applied, which is a fixed text 20%

calculated_tax-the tax calculated, rounded to two decimal places, including trailing zeros if necessary, e.g., 500.00

The results should be sorted in ascending order by iban.

Note:

Only income in 2023 should be included.

Here, are sample table data of Account and Income Tables are

Sample table data are

accounts

id

iban

1

2

3

FR55 4477 6154 73ND TN3F HMOU T36

DK46 1272 1831 2573 01

RS53 5237 579460165411 43

income

account_id

1

1

dt

amount

2022-12-31 10:03:42

2779.19

2023-02-04 08:50:14

1777.68

2023-02-13 04:22:07

1954.81

2023-03-04 14:46:04

1547.79

2023-05-23 15:42:13

1208.49

2023-05-24 23:24:07

1521.72
1

2023-07-28 11:01:46

1792.75

1

2023-12-07 14:19:09

2374.25

1

2024-01-27 05:55:36

2803.39

2

2022-12-03 18:04:34

1826.65

2

2023-02-17 00:59:57

3074.11

2

2023-03-01 08:17:15

1007.30

2

2023-08-19 09:16:41

4515.04

2

2024-01-08 04:14:22

33278

2

2024-01-10 15:16:28

2033.87

3

2023-05-09 07:28:27

3158.66

3

2023-05-22 04:39:34

3851.20

3

2023-07-21 19:51:14

4152.29

3

2023-10-05 05:42:49

4722.20

3

2023-11-11 02:42:59

1592.16

Expected Output is.
Sample Output

+——————————+————–+———-+—————-+
| iban | total_income | tax_rate | calculated_tax |
+——————————+————–+———-+—————-+
| DK46 1272 1831 2573 01 | 8596.45 | 20% | 1719.29 |
| FR55 4477 6154 73ND TN3F HMOU T36 | 12176.48 | 20% | 2435.30 |
| RS53 5237 579460165411 43 | 17476.51 | 20% | 3495.30 |
+——————————+————–+———-+—————-+

Query I used to get this Output is-

SELECT 
    a.iban,
    ROUND(SUM(i.amount), 2) AS total_income,
    '20%' AS tax_rate,
    ROUND(SUM(i.amount) * 0.20, 2) AS calculated_tax
FROM 
    accounts a
JOIN 
    income i ON a.id = i.account_id
WHERE 
    YEAR(i.dt) = 2023
GROUP BY 
    a.iban
ORDER BY 
    a.iban ASC;

But this query gives wrong output. Can someone knows the output or expert in SQL here to answer the above question’s query?
Thanks.

I tried query which not giving correct output.