plsql sum another table values to joined tables
You can use analytic functions rather than GROUP BY
and aggregating:
select s.studentname,
s.tax,
invoiceamount,
SUM(d.amount) OVER (PARTITION BY s.invoiceno) AS inv_amt_calc,
d.product,
d.amount,
SUM(CASE WHEN c.code IS NOT NULL THEN d.amount END)
OVER (PARTITION BY s.invoiceno) AS taxexclude
from student s
INNER JOIN studentdetail d
ON s.invoiceno = d.invoiceno
LEFT OUTER JOIN specialcodes c
ON (c.code = d.code)
Note: You can (and should) calculate the invoice amount from the studentdetails
table rather than duplicating the data in the student
table and violating Third-Normal Form.
Which, for your sample data, outputs:
STUDENTNAME TAX INVOICEAMOUNT INV_AMT_CALC PRODUCT AMOUNT TAXEXCLUDE Paul 500 1950 1950 rubber 350 450 Paul 500 1950 1950 pencil 100 450 Paul 500 1950 1950 bag 1500 450 Georghe 1000 6850 6850 tv 1500 350 Georghe 1000 6850 6850 wheel 5000 350 Georghe 1000 6850 6850 ps 300 350 Georghe 1000 6850 6850 mouse 50 350 Mary 1500 1900 1900 bag 1500 400 Mary 1500 1900 1900 keyboard 400 400 Messy 2000 7050 7050 mouse 50 50 Messy 2000 7050 7050 car 7000 50
If you really want a version using GROUP BY
then:
SELECT s.studentname,
s.tax,
s.invoiceamount,
SUM(d.amount) OVER (PARTITION BY s.invoiceno) AS inv_amt_calc,
d.product,
d.amount,
t.taxexclude
FROM student s
INNER JOIN studentdetail d
ON s.invoiceno = d.invoiceno
LEFT OUTER JOIN (
SELECT invoiceno,
SUM(amount) AS taxexclude
FROM studentdetail
WHERE code IN (SELECT code FROM specialcodes)
GROUP BY
invoiceno
) t
ON s.invoiceno = t.invoiceno;
db<>fiddle here